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
gluizqueiroz
Resolver I
Resolver I

How to make a measure to filter only values that matched with all selected values and follow a rule

I have the following real table:

 

StoreId_ProductSold
ShoppingF10NO
StreetF10YES
MarketF10NO
ShoppingF20NO
StreetF20YES
MarketF20YES
ShoppingF30YES
StreetF30YES
MarketF30NO

 

I have a slicer for Store on my page.

 

I need to make a measure that returns only products with Sold = YES on ALL Stores selected.

 

If user selects "Shopping" and "Street" for example, I need to show on a visual table  the following result:

 

Id_ProductMeasure
F100
F200
F301

 

Like I said, the user selected "Shopping" and "Street" and the measure returns 1 only for Id_Product = F30, because it is the only product that have sold on "Shopping" and "Street". 

Id_Product = F10 returns 0 because it doesn't sold on "Shopping"
Id_Product = F20 returns 0 because it doesn't sold on "Market" and "Shopping"
Id_Product = F20 returns 1 because it sold on "Market" and "Shopping".

I will use that measure to show on table just the values with 1, in other words, just products that have sold on all Stores selected.

1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

@gluizqueiroz 

 

Try this MEASURE

 

Measure =
IF (
    ISEMPTY (
        EXCEPT (
            VALUES ( Table1[Store] ),
            CALCULATETABLE ( VALUES ( Table1[Store] ), Table1[Sold] = "Yes" )
        )
    ),
    1,
    0
)

Regards
Zubair

Please try my custom visuals

View solution in original post

2 REPLIES 2
Zubair_Muhammad
Community Champion
Community Champion

@gluizqueiroz 

 

Try this MEASURE

 

Measure =
IF (
    ISEMPTY (
        EXCEPT (
            VALUES ( Table1[Store] ),
            CALCULATETABLE ( VALUES ( Table1[Store] ), Table1[Sold] = "Yes" )
        )
    ),
    1,
    0
)

Regards
Zubair

Please try my custom visuals

Hey @Zubair_Muhammad

I tried your solution and it work correctly. 

Thanks man, you are awesome!

Have a nice day!

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.