Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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!
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".
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!
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".
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.
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!
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |