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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
roshak
Frequent Visitor

Ignore rows where fact doesn't sum to zero?

Given the following table:

IDDIM1DIM2FACTCOMMENT
1ATEST-5.00SHOULD BE HIDDEN
2ATEST5.00SHOULD ALSO BE HIDDEN
3BABC-1.30IGNORED NOTES
4BABC1.30MORE IGNORED NOTES
5CTEST34.00SHOULD BE VISIBLE
6ATEST2-2.20NOT SHOWN
7ATEST22.20MORE NOT SHOWN
8BABCD7.20SHOULD BE SEEN
9BABC1.30VISIBLE

What is the best technique(s) to "ignore rows whose facts cancel out"? 

 

Prefered result:

IDDIM1DIM2FACTCOMMENT
5CTEST34SHOULD BE VISIBLE
8BABCD7.20SHOULD BE SEEN
9BABC1.30VISIBLE

I had considered doing it in PowerQuery via a groupby DIM1/DIM2/SUM(FACT) WHERE SUM(FACT) is not 0 and then rejoin back to the previous rows to bring the ID and COMMENT back in.

 

However, I thought there may be a DAX alternative as well.

 

Link to example

3 REPLIES 3
amitchandak
Super User
Super User

@roshak , Try a measure like

 

measure =
var _sum = calculate(sum(Data1[FACT]), filter(allselected(Data1), Data1[dim1] = max(Data1[dim1])  && Data1[dim2] = max(Data1[dim2]) && abs(Data1[FACT]) = abs(MAX(Data1[FACT])) ))
return
calculate(sum(Data1[FACT]),filter(Data1, _sum >0))
 
amitchandak_0-1681276069126.png

 

I thought this was exactly what I needed until my real data surfaced another scenario.  In some cases there will be multiple rows for the DIMs and any that aren't negated by a single exact match should remain.  

IDDIM1DIM2FACTCOMMENT
1ATEST-5.00SHOULD BE HIDDEN
2ATEST5.00SHOULD ALSO BE HIDDEN
3BABC-1.30IGNORED NOTES
4BABC1.30MORE IGNORED NOTES
5CTEST34.00SHOULD BE VISIBLE
6ATEST2-2.20NOT SHOWN
7ATEST22.20MORE NOT SHOWN
8BABCD7.20SHOULD BE SEEN
9BABC1.30VISIBLE

 

Desired result:

IDDIM1DIM2FACTCOMMENT
5CTEST34SHOULD BE VISIBLE
8BABCD7.20SHOULD BE SEEN
9BABC1.30VISIBLE

 

I have updated the sample file and will update the original post.

that is exactly what I was looking for.  I used a similar approach in PowerQuery-M but needed the flexibility to have more than one type of MEASURE that allowed for different DIM column evaluations for the same logic.  (i.e., one measure includes MONTH in the dimensions and another does not).  thanks!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.