String measure results in evaluation for non-existent relationships

Anything related to PowerPivot and DAX Formuale
jayhawk
Posts: 3
Joined: Thu Jul 12, 2018 11:22 pm

String measure results in evaluation for non-existent relationships

Postby jayhawk » Thu Jul 12, 2018 11:35 pm

I want to build a pass / fail result measure on a star schema - 1 fact table, 1 dimension table

I can do it, but the measure evaluates relationships that *don't exist* and I have no idea why.

I can filter the results by another measure, but I feel I need to understand this behavior.

I've attached a simple Power BI dashboard with a constant "foobar" string measure to show the behavior.

Can anyone tell me why I am seeing this?

Thanks!

-J
Attachments
Strange_Measure_Behavior.pbix
(36.28 KiB) Downloaded 26 times

MattAllington
Posts: 983
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: String measure results in evaluation for non-existent relationships

Postby MattAllington » Fri Jul 13, 2018 8:34 am

Effectively yes there is a crossjoin. The job of the relationship is to propagate filters from the one side to the many side. When you place 2 columns from 2 separate source tables in a visual, it creates a crossjoin and then filters out the relationships that don't exists (because of filter propagation). When you write a constant measure, there is a "valid result" for every combination in the crossjoin, which is why these other records show up.
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

jayhawk
Posts: 3
Joined: Thu Jul 12, 2018 11:22 pm

Re: String measure results in evaluation for non-existent relationships

Postby jayhawk » Sat Jul 14, 2018 12:21 am

Thanks Matt!

Is there a recommended way to wrap the constant in a Dax function that says "only evaluate this for existing relationships"?

Oxenskiold
Posts: 35
Joined: Tue Jan 05, 2016 10:38 pm

Re: String measure results in evaluation for non-existent relationships

Postby Oxenskiold » Mon Jul 16, 2018 6:52 pm

Hi Jayhawk,
I know of no recommended way to do what you suggest. However you can use this general measure:

Code: Select all

GlobalAutoExists =
IF ( NOT ( ISBLANK ( SUM ( Facttable[facttablenumericalcolumn] ) ) ), "Foobar" )

alternatively:

Code: Select all

GlobalAutoExists2 =
IF ( NOT ( ISEMPTY ( Facttable) ), "Foobar" )


so in your case:

Code: Select all

GlobalAutoExists =
IF ( NOT ( ISBLANK ( SUM ( Cars[VALUE] ) ) ), "Foobar" )


Code: Select all

GlobalAutoExists2 =
IF ( NOT ( ISEMPTY ( Cars ) ), "Foobar" )


Best regards Jes

jayhawk
Posts: 3
Joined: Thu Jul 12, 2018 11:22 pm

SOLVED: String measure results in evaluation for non-existent relationships

Postby jayhawk » Tue Jul 17, 2018 10:57 pm

Jes,

Thank you!

Your IF ( NOT ( ISEMPTY ( Facttable) ), "Foobar" ) solution was graceful and effective.

Marking this SOLVED.


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: agraham and 7 guests