Flag all IDs from a certain category

[AndreiiBC]

Hello,

I had a separate topic here which I didn't find a solution for but I was thinking I can do things in a different way so the problem is:

I want to flag all the ID's from a certain category, if that category contains an ID with a specific condition. To be more clear, take a look below:

Here I wanted to flag with 1 every record for the subcategory called MIS, because MIS contains at least 1 Product "Discounted". But as you can see in the picture, only the rows which have "Discounted" in the Name have been flageed, the rest are 0.

So the expected result would have been for all the rows under MIS to have the flag 1.

The code I used:

Flag_Discounted = 
VAR Discounted_Products  =
    SELECTCOLUMNS (
        FILTER (
            DIM,
            CONTAINSSTRING ( DIM[Product_Name], "discounted" )
        ),
        "ProductID", DIM[Product_ID]
    ) 

VAR ListofSubcat =
    SELECTCOLUMNS (
        FILTER (
            'FACT',
            'FACT'[Product_ID] IN Discounted_Products
        ),
        "SubcatName", RELATED( DIM[Subcategory_Name])
    )

VAR SubcatID =
    SELECTCOLUMNS (
        FILTER ( DIM, DIM[Subcategory_Name] IN ListofSubcat ),
        "SubcatID", DIM[Subcategory_ID]
    )

RETURN 
    IF(HASONEVALUE('FACT'[Price]), -- to only show the flag for the IDs with values in the FACT
    IF(MAX('FACT'[Subcategory_ID]) IN SubcatID, 1, 0),
    BLANK()
    )

PBI file also attacked, link here: https://we.tl/t-0Y3wW0QFfs

Any idea how can I solve this or what am I doing wrong here ?

Thank you!

[ekoland · Accepted solution]

First image is without any selection in the slicer. As you can see the flag is working fine for the Products that have Details as "Discounted".

Second image AAA2 product is selected and the flag shows 0 for everything.
Third Image  AAA3 product is selected amd the flag shows 1 for AAA3.

I hope that this will help you or a least drive you to the right path!

[ekoland · Accepted solution]

First image is without any selection in the slicer. As you can see the flag is working fine for the Products that have Details as "Discounted".

Second image AAA2 product is selected and the flag shows 0 for everything.
Third Image  AAA3 product is selected amd the flag shows 1 for AAA3.

I hope that this will help you or a least drive you to the right path!

[ekoland · Accepted solution]

Try this:

Flag = 
VAR _table =
    SUMMARIZE (
        'Fact',
        'Fact'[ProductID],
        dim[Subcategory],
        dim[Details],
        "new column", IF(CONTAINSSTRING(dim[Details],"Discounted"),1,0))
    
VAR _flag= IF(MAXX(SUMMARIZE(_table,dim[Subcategory],[new column]),[new column])=1,"Discounted","Non Discounted")
RETURN
    _flag

Now if a product has "Discounted" the entire Subcategory is "Discounted". 

[samratpbi]

Hi, you may use conditional column like:
Flag_Discounted = IF(Subcategory_Name = "MIS"),1,0)

If this resolves your problem then please mark it as Solution, Thanks.

[AndreiiBC]

Hi, I can't use a calculated column because I am using Direct Query in my original data model so I can't add any new columns, I need to use a measure.

Also, I want the flag to be dynamic, I can't hardcode the value like in your formula, MIS was just an example that I used.

Thank you!