Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello all,
I have a problem that I've been trying to figure out. I have a table with three columns (sample data shown below):
I am trying to create a calculated table from the above wherein, row elements have the above product ID if the operations involved are A, B and C.
Sample output:
Any help is highly appreciated.
Thanks in advance!
M
Solved! Go to Solution.
Hi Midmurali,
My approach would be to do this at the Power Query level.
Using your own sample Data, open Power Query.
Steps below:
I am hoping this way isn't too involved. There may be an easier way that others can find.
I have uploaded the PBIX file here if you want to have a look!
https://www.dropbox.com/s/lpjuhuro5f4i7wf/Midmurali%20-%20Sample%20Solution.pbix?dl=0
Good luck!
Try a measure along the lines of:
All A B C =
VAR _List = {"A", "B", "C"}
VAR _ListRows = COUNTROWS(_list)
VAR _ABC = CALCULATETABLE(VALUES(fTable[Operation]), ALLEXCEPT(fTable, fTable[ProductID]))
VAR _Int = COUNTROWS(INTERSECT(_ABC, _List))
RETURN
IF(_Int = _ListRows, 1)
(If you need this calculation by date, include the date field in the ALLEXCEPT expression in VAR _ABC)
Proud to be a Super User!
Paul on Linkedin.
Hi @midmurali - Pls find attached the PBIX link for reference. Please mark accepted if this fixed your requirement. Many Thanks
Try a measure along the lines of:
All A B C =
VAR _List = {"A", "B", "C"}
VAR _ListRows = COUNTROWS(_list)
VAR _ABC = CALCULATETABLE(VALUES(fTable[Operation]), ALLEXCEPT(fTable, fTable[ProductID]))
VAR _Int = COUNTROWS(INTERSECT(_ABC, _List))
RETURN
IF(_Int = _ListRows, 1)
(If you need this calculation by date, include the date field in the ALLEXCEPT expression in VAR _ABC)
Proud to be a Super User!
Paul on Linkedin.
Hi, @midmurali If I understand your request correctly, please see the attached file
Hello @DimaMD
I think you have misunderstood my problem. My output should contain product IDs if operations involved are A, B and C. Output is irrespective of values of product ID.
Hi Midmurali,
My approach would be to do this at the Power Query level.
Using your own sample Data, open Power Query.
Steps below:
I am hoping this way isn't too involved. There may be an easier way that others can find.
I have uploaded the PBIX file here if you want to have a look!
https://www.dropbox.com/s/lpjuhuro5f4i7wf/Midmurali%20-%20Sample%20Solution.pbix?dl=0
Good luck!
No problem! Please mark my solution as accepted if helpful.
Cheers!
In my solution, I have ensured that all operations A, B&C have been done for each product.
if the data set is as under where ProductID 100 and 104 have all the operations
Dataset 1
Then Output will be
Suppose if the data set is as under where Product ID has Operation A repeated twice, then my output will not list ProductID 100, it will only list Product ID 104.
Dataset 2
Output 2
If this meets your requirement please let me know I will share the PBIX file.
Hi Manoj,
This is exactly what I am looking for. I need the associated dates and product ids which have all three operations involved (A, B and C).
Please share the pbix file if you can!
Thank you
Hi @midmurali - Pls find attached the PBIX link for reference. Please mark accepted if this fixed your requirement. Many Thanks
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |