topic Re: Measure to subtract a count when two filters applied in DAX Commands and Tips

Measure to subtract a count when two filters applied

Anonymous — Tue, 21 Nov 2023 22:34:42 GMT

Hello,

I am trying to create a measure in Power BI which when used with two filters, identifies the number of times both filters are selected for one object, and then removes this number from the overall count. Let me explain with my example.

I have a group of people who play 3 sports (Baseball, Boxing and Soccer). I need to create a filter so that I can select one sport (eg Baseball - 128 people play baseball), and then have a 2nd filter which Identifies another sport (eg Boxing - 64 people box).

The measure I need to create identifies the people who do both sports (eg People who Both play baseball and Box - only 22) and subtracts this number from the first filter (eg Baseball - 128 People). The result would be personnel who play baseball but do not box (final result 106).

I am new to Power Bi and I believe a measure is the method to use, but I have no idea how to do this. Please help.

Re: Measure to subtract a count when two filters applied

Anonymous — Wed, 22 Nov 2023 06:42:04 GMT

Hi, DAX23

May I ask if this is the expected output you are looking for? Based on your description, I have created many measures to achieve the effect you are looking for. Following picture shows the effect of the display.

Measures:

Count_baseball_boxing = CALCULATE(COUNTROWS('Table'),'Table'[BASEBALL]>0&&'Table'[BOXING]>0) Count_baseball_soccer = CALCULATE(COUNTROWS('Table'),'Table'[BASEBALL]>0&&'Table'[SOCCER]>0) Count_boxing_soccer = CALCULATE(COUNTROWS('Table'),'Table'[BOXING]>0&&'Table'[SOCCER]>0) Diff_baseball_boxing = SUM('Table'[BASEBALL])-[Count_baseball_boxing] Diff_baseball_soccer = SUM('Table'[BASEBALL])-[Count_baseball_soccer] Diff_boxing_baseball = SUM('Table'[BOXING])-[Count_baseball_boxing] Diff_boxing_soccer = SUM('Table'[BOXING])-[Count_boxing_soccer] Diff_soccer_baseball = SUM('Table'[SOCCER])-[Count_baseball_soccer] Diff_soccer_boxing = SUM('Table'[SOCCER])-[Count_boxing_soccer] Filter = IF(SELECTEDVALUE('Sports1'[Sport])=MAX('Sports2'[Sport]),1)

Measure = VAR _sport1 = SELECTEDVALUE('Sports1'[Sport]) VAR _sport2 = SELECTEDVALUE('Sports2'[Sport]) VAR _result = SWITCH( TRUE(), ISBLANK(_sport1) || ISBLANK(_sport2),"Please selcet the sports", _sport1=_sport2,0, _sport1="Baseball" && _sport2="Boxing",[Diff_baseball_boxing], _sport1="Baseball" && _sport2="Soccer",[Diff_baseball_soccer], _sport1="Boxing" && _sport2="Soccer",[Diff_boxing_soccer], _sport1="Boxing" && _sport2="Baseball",[Diff_boxing_baseball], _sport1="Soccer" && _sport2="Baseball",[Diff_soccer_baseball], _sport1="Soccer" && _sport2="Boxing",[Diff_soccer_boxing] ) RETURN _result

If this does not work, could you please share some sample data without sensitive information and expected output.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data in the Power BI Forum

Re: Measure to subtract a count when two filters applied

Anonymous — Wed, 22 Nov 2023 17:01:38 GMT

Thank you for your hard work on this. The measure you proposed is much larger than what I would have ever thought. I believed it would be something like a measure to count the names once a sport was selected, then another measure to count the identical names once the 2nd filter is selected, and subtract those duplicates. Apparently i have a lot to learn regarding measures.

Where I am working I am unable to provide sample data at this time. I will post the sample data in a few hours.

Re: Measure to subtract a count when two filters applied

Anonymous — Tue, 12 Dec 2023 07:56:24 GMT

Hi, Anonymous

Have you solved this issue or any updates? If the above method did not solve your issue, you can provide sample data through the following.

You can post data tables (that do not contain sensitive information and cover the question), measures, and expected outputs in the Reply.
The other option is to put your files in a dropbox, onedrive or googledrive folder and share the link with the community.
Please refer to the related links for details:

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards,
Yang
Community Support Team

How to get your questions answered quickly -- How to provide sample data in the Power BI Forum