Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
jader3rd
Employee
Employee

A Measure that depends upon the result of another measure

I have rows where certain values are interesting, and I want to split the data into HasInteresting with counts for true or false. The trick is that it's not if the row is interesting, but if any row in a certain grouping has an interesting row.

So if I have something like the below:

ObjectNameSeriesReasonDuration
ObjAXMild12
ObjAXInteresting454
ObjBYMediocre12
ObjBYMild32
ObjCXMild87
ObjCXBoring37

I've added a calculated column of IsInteresting = IF([Reason] = "Interesting", 1, 0), and then a measure of HasInteresting = SUM([IsInteresting]) > 0. Which generates the following correct result.

ObjectNameHasInteresting
ObjATrue
ObjBFalse
ObjCFalse

But what I want to know is "How many ObjectName's have an HasInteresting of True, and how many have False by Series".

It would look something like

SeriesHasInterestingTrueHasInterestingFalse
X11
Y01

 

I tried doing something with COUNTROWS, but that returned results from the original rows, not from the middle table.

1 ACCEPTED SOLUTION
AntrikshSharma
Community Champion
Community Champion

@jader3rd  Try this:

HasInterestingTrue = 
CALCULATE (
    COUNTROWS ( DISTINCT ( Jader[ObjectName] ) ),
    Jader[Reason] = "Interesting"
) + 0
HasInterestingFalse = 
VAR ObjectThatHasInteresting =
    CALCULATETABLE ( DISTINCT ( Jader[ObjectName] ), Jader[Reason] = "Interesting" )
VAR Result =
    CALCULATE (
        COUNTROWS ( DISTINCT ( Jader[ObjectName] ) ),
        Jader[Reason] <> "Interesting",
        NOT Jader[ObjectName] IN ObjectThatHasInteresting
    )
RETURN
    Result

1.PNG2.PNG

 

View solution in original post

17 REPLIES 17
AntrikshSharma
Community Champion
Community Champion

@jader3rd  Try this:

HasInterestingTrue = 
CALCULATE (
    COUNTROWS ( DISTINCT ( Jader[ObjectName] ) ),
    Jader[Reason] = "Interesting"
) + 0
HasInterestingFalse = 
VAR ObjectThatHasInteresting =
    CALCULATETABLE ( DISTINCT ( Jader[ObjectName] ), Jader[Reason] = "Interesting" )
VAR Result =
    CALCULATE (
        COUNTROWS ( DISTINCT ( Jader[ObjectName] ) ),
        Jader[Reason] <> "Interesting",
        NOT Jader[ObjectName] IN ObjectThatHasInteresting
    )
RETURN
    Result

1.PNG2.PNG

 

This works. But now that I have your attention, I would like your thoughts on where my thinking has gone wrong with something I was trying a few hours ago. I thought that I could create a Measure using GROUPBY and make use of that.

Measure = GROUPBY(jader, jader[ObjectName], "HasInteresting", MAXX(CURRENTGROUP(), IF(jader[Reason], 1, 0)))

The above errors out with "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value". What am I not understanding about GROUPBY?

@jader3rd  GROUPBY is a table function, your measure returns the below table, and implicitly DAX can only convert 1 row and 1 column table into a scalar value. Hence your measure throws an error.

 

Also had to do a comparision inside IF otherwise it won't work.

3.PNG

Okay. I normally figure out the formulas by solving an inner part and then wrapping it in the next level. That approach apparently doesn't work with GROUP BY.

What would I wrap the GROUP BY in to make the Measure?

@jader3rd That's where things always go wrong, I have stopped thinking of DAX as excel formulas or any other programming language, because you have to think holistically and not just from inside out.

 

GROUPBY is useful when you have created temporary columns on the fly and you want to summarize the result by those columns but aren't able to do so because they do not carry the data lineage of a column of the model. CURRENTGROUP helps in accessing the current row context and then you can apply SUMX, COUNTX or any either iterator to get the result for the values created virtually.

 

But here is how you can make your version work.

JaderM =
SUMX (
    GROUPBY (
        jader,
        jader[ObjectName],
        Jader[Reason],
        "HasInteresting", SUMX ( CURRENTGROUP (), IF ( jader[Reason] = "Interesting", 1, 0 ) )
    ),
    [HasInteresting]
)

1.PNG

That does get me the count for HasInteresting, but it's not working for the count of objects names with no interesting reasons.

I tried

Measure2 = COUNTROWS( FILTER(GROUPBY (jader, jader[ObjectName], jader[Reason], "HasInteresting", MAXX( CURRENTGROUP(), IF (jader[Reason] = "Interesting", 1, 0))), [HasInteresting] = 0))

But that gives the count of the underlying rows that doesn't have interesting. Which boggles my mind. The GROUPBY should group on object name and return a 3 row table to the Filter. The Filter statement should then filter out the single row with HasInteresting = 1, and return two rows. The COUNTROWS should then count the two remaining rows. Obviously I have something backwards. 

You can try to debug your measure step by step in a table and you can see the rows returned by GROUPBY to FILTER

I see what I did wrong now. I put jader[Reason] in the GROUP BY, even though I shouldn't have. Now it all makes sense.

Different way of thinking @AntrikshSharma 👍

My approach was to calculate each one the same way and just look at the results of the calculated table. But the hard part was getting any object name that had an "interesting" in it and excluding it for all same object names that didn't. You just set that to a variable, then excluded it from your final results.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Thanks for your trial and error with this @edhans. It means a lot to me.


@jader3rd wrote:

Thanks for your trial and error with this @edhans. It means a lot to me.


Glad to help @jader3rd 

 

The approach of @AntrikshSharma is really common sense from the standpoint of build result a, then result b and exclude result a from b.

 

But I was so far down the path mentally of building the tables in my original solution, I didn't see the forest for the trees. 😁

 

But I did get a blog post out of it. Found out some weird behavior using NOT as an operator and function, which it can do both, so all is not lost.

Kudos appreciated, and glad you have a working solution!



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
edhans
Super User
Super User

@jader3rd I would try to avoid the intermedary tables and calculated columns if possible. Try something like this, which is a single measure:

Interesting Count = 
VAR varInterstingCount =
COUNTX(
    FILTER(
        'Table',
        'Table'[Reason] = "Interesting"
    ),
    'Table'[Reason]
)
RETURN
COALESCE(varInterstingCount, 0)

edhans_0-1601422875292.png

In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
SQLBI Video on Measures vs Calculated Columns

Does that help? 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

That gets me the interesting count, but I also need the uninteresting. I am trying to do a comparison of how many Objects have any rows which are interesting, vs how many do not have any interesting rows.

Just a tweak and 2nd measure @jader3rd 

UnInteresting Count = 
VAR varInterstingCount =
COUNTX(
    FILTER(
        'Table',
        'Table'[Reason] <> "Interesting"
    ),
    'Table'[Reason]
)
RETURN
COALESCE(varInterstingCount, 0)

edhans_0-1601424546742.png

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

That's still the Count of Rows, not the Count of ObjectNames which do contain an interesting Reason, and those which don't at all. The total sum in the final table should be 3, because there are 3 ObjectNames.

Ok @jader3rd - I didn't understand fully the original request. So this is a bit longer,but still two measures.

Interesting Count = 
COUNTX(
    FILTER(
        ADDCOLUMNS(
            SUMMARIZE(
                'Table',
                'Table'[ObjectName],
                'Table'[Series]
            ),
            "Reasons",
                CONTAINSSTRING(
                    CALCULATE(
                        CONCATENATEX(
                            'Table',
                            'Table'[Reason],
                            ", "
                        )
                    ),
                    "Interesting"
                ) * 1
        ),
        [Reasons] = 1
    ),
    [Reasons]
)

and

UnInteresting Count = 
COUNTX(
    FILTER(
        ADDCOLUMNS(
            SUMMARIZE(
                'Table',
                'Table'[ObjectName],
                'Table'[Series]
            ),
            "Reasons",
                CONTAINSSTRING(
                    CALCULATE(
                        CONCATENATEX(
                            'Table',
                            'Table'[Reason],
                            ", "
                        )
                    ),
                    "Interesting"
                ) * 1
        ),
        [Reasons] = 0
    ),
    [Reasons]
)

edhans_0-1601437543143.png

It does this by building a virtual table first that looks like this:

edhans_1-1601437611281.png

which comes from this - you can see this in DAX Studio, or you could create a new calculated table with this code to see it:

ADDCOLUMNS(
    SUMMARIZE(
        'Table',
        'Table'[ObjectName],
        'Table'[Series]
    ),
    "Reasons",
        CONTAINSSTRING(
            CALCULATE(
                CONCATENATEX(
                    'Table',
                    'Table'[Reason],
                    ", "
                )
            ),
            "Interesting"
        )
)

edhans_2-1601437691506.png

then the rest just converts the true/false to 1/0, then it counts those.

I do wonder if @Greg_Deckler or @TomMartens could shorten this up a bit though.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Wow... my spelling is horrible.... 😂



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors