topic Only show measure value for certain row value in Matrix in DAX Commands and Tips

Only show measure value for certain row value in Matrix

Anonymous — Sat, 25 Mar 2023 16:57:16 GMT

Hi All

I need some help with a measure I have created showing correctly for a given row value on a matrix visual. In my model I have 3 tables (Date, Reporting, and Financial). Date and Reporting are Dims and Finacial is the Fact. I have a measure called TotalExpenseAmt which is structured like this:

TotalExpenseAmt =

CALCULATE(SUM(Financial[Amount]),Reporting[LOSGroup] IN {"EXPENSES"})

One of the attributes in Reporting is LOSGroup. When I have "TOTAL EXPENSES" on the row I would like to display this measure value. Otherwise I just want to show the value for Amount. Displaying my measure shows this for all row values. How would I switch it to only show for the "TOTAL EXPENSE" row. For the highlighted value I want it to show. Also show the Detail values for Expenses such as "GP&T", "REPAIRS", etc. That's fine. But for say "CAPITAL" i want it to be blank

I have the pbix file here SampleLOSGroup.pbix

This seems like a simple thing but my iterations with this measure just havent been successful right now. Appreciate any help.

Re: Only show measure value for certain row value in Matrix

Greg_Deckler — Sat, 25 Mar 2023 17:03:10 GMT

Anonymous Try this:

TotalExpenseAmt = VAR __LOSGroup = MAX('Reporting'[LOSGroup]) VAR __Result = IF( __LOSGroup = "TOTAL EXPENSES", CALCULATE(SUM('Financial[Amount']),'Reporting'[LOSGroup] IN {"EXPENSES"}), SUM('Financial'[Amount]) ) RETURN __Result

Re: Only show measure value for certain row value in Matrix

Anonymous — Sat, 25 Mar 2023 17:05:50 GMT

Thanks Greg. That appears to work. I appreciate it

Re: Only show measure value for certain row value in Matrix

Anonymous — Mon, 27 Mar 2023 14:45:19 GMT

Greg_Deckler I think my initial problem is that the relationship between the Reporting and Financial tables is "Both" (i.e. bi-directional). If we switch it to that you can see the issue:

If I am forced to have bi-directional between these tables how can I get that measure to work?