The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hiya,
I am trying to write a measure to count the Products per customer and per id, where the "Product" column = "Product 1" and does not contain any "Product 2" for the same ID.
here s my attempt
Measure =
CALCULATE(COUNTA(Table1[Product]),
FILTER(Table1, Table1[Product] = "Product 1"), FILTER(Table1,
NOT(CONTAINSSTRING(Table1[Product],"Product 2"))))
I am getting the result count of 3, but I am looking only for 2 as a result (example below)
Sample Data:
IdCustomerProduct
1 | Customer 1 | Product 1 |
1 | Customer 1 | Product 2 |
1 | Customer 1 | Product 3 |
1 | Customer 1 | Product 4 |
2 | Customer 1 | Product 1 |
2 | Customer 1 | Product 4 |
2 | Customer 1 | Product 5 |
3 | Customer 1 | Product 1 |
3 | Customer 1 | Product 8 |
3 | Customer 1 | Product 3 |
3 | Customer 1 | Product 4 |
Thanks
Solved! Go to Solution.
Hi,
thank you for your feedback.
I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file whether it suits your requirement.
Hi @Jihwan_Kim ,
I am still getting the same result for 3 records, and my goal is to show only 2 records
Goal:
Hi,
thank you for your feedback.
I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file whether it suits your requirement.
@Jihwan_Kim Super Star🌟!! Thank you very much. I have to say, formatting DAX does help a lot in understanding the logic behind the code. 👍
Hi,
Please check the below picture and the attached pbix file, whether it suits your requirement.
Expected result measure: =
COUNTROWS (
FILTER (
ADDCOLUMNS (
DISTINCT ( 'Table'[Id] ),
"@productonecondition", CALCULATE ( COUNTROWS ( FILTER ( 'Table', 'Table'[Product] = "Product 1" ) ) ),
"@producttwocondition", CALCULATE ( COUNTROWS ( FILTER ( 'Table', 'Table'[Product] = "Product 2" ) ) )
),
[@productonecondition] >= 1
&& [@producttwocondition] = 0
)
)
User | Count |
---|---|
12 | |
9 | |
6 | |
6 | |
6 |
User | Count |
---|---|
24 | |
14 | |
14 | |
9 | |
7 |