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
Anonymous
Not applicable

Measure : GROUP BY and AND conditions

powerbi.PNGHi,

 

This may be very trivial question but I cannot figure out how to do it using a measure. I have this table A which is filtered using column A. Result 1 and result 2 have additional condition on column B. One shows only software and the second one only hardware. Now the point is that Result 3 should show only IDs that appear both in Result 1 and Result two. So if I choose 1234 and 12345 using my filter, I get ID 102(because it has both software and hardware). I cannot do it like this...

 

Measure = CALCULATE(COUNTROWS(MyTable);AND(MyTable[B]="SOFTWARE";MyTable[B]="HARDWARE")

If it would be calculated column, I would try to use EARLIER but I don't know how to do it using a measure.

 

Thank you for any advice

4 REPLIES 4
MFelix
Super User
Super User

Hi @Anonymous ,

 

To what I can understand this as only to do with interaction between visuals, if your slicer visual filters out the Result 3 table you should only get the 102 result from your table.

 

filter.gif

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Hi @MFelix , thank you for your answer!

 

But then it shows results also when either of the values is selected. And Result 3 table is meant to show something only if it exists in both Result 1 and Result 2 tables.

 

 

Hi @Anonymous ,

 

Create the following measure:

 

Count_ID = 
IF (
    CALCULATE ( COUNT ( Table1[ID] ); ALL ( Table1[A] ) ) > 1;
    1;
    BLANK ()
)

Then add this as a filter on your ID table and select all non blank that will give you the result,

 

Another option is to use:

Count_ID = 
IF (
    CALCULATE ( COUNT ( Table1[ID] ); ALLSELECTED ( Table1[A] ) ) > 1;
    1;
    BLANK ()
)

In this case if you select 1234 it will not show any result if you select 1234 and 12345 it will show the 102.

 

Check PBIX file attach.

 

Regards,

MFelix

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Hi, 

 

@MFelix, It works fine. However, another problem is that in my original dataset I have some more columns. Therefore ID is not unique.

So in this case, the measure will be right(count[id]>1) but expected result will be wrong.problem.PNG

Is it possible to check whether for specific ID exists both "HARDWARE" and "SOFTWARE" values? 

 

Best Regards!

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.