Display table contents for particular column value with conditions

[midmurali]

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

[MDodds · Accepted 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:

1. Reference your data table.
   In the left hand query section, right click on your table and select reference per the below. Best to use reference not duplicate so you aren't running any prior transformations twice.
   Referencing will create a duplicate of your current sample data that we can run the next transformations on.
   I have renamed the reference table in my example to "Summary Table".
   
2. Step two - Pivot Columns.
   In the transformations tab, select "Pivot Column", then use Date as your Values Column and Count (ALL) as your aggregate value function.
   
3. Add the three new columns together using a new column per below.
   
4. Delete the A, B and C columns as unnecessary.
   
   
5. Filter the "All Operations" Column to only include items with the number 3.
   
   This will give you the result you want but without the dates.
   
6. To add the dates back in, we can hit "Close and Apply" and head out of Power Query.
   Jump into your model view and set up the relationship between your old table and the new summary table. The join should be on ProductID to ProductID with a many to 1 relationship.
   
7. And thats it!
   Now if you go into the report view, select the table visual and use the Date Field from your original data table, with the ProductID field from your summary table. Open the visual filters and set ProductID to "Is Not Blank" And you get the result 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!

[PaulDBrown · Accepted solution]

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)

[PaulDBrown · Accepted solution]

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)

[DimaMD]

Hi, @midmurali If I understand your request correctly, please see the attached file

[midmurali]

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.

[MDodds · Accepted 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:

1. Reference your data table.
   In the left hand query section, right click on your table and select reference per the below. Best to use reference not duplicate so you aren't running any prior transformations twice.
   Referencing will create a duplicate of your current sample data that we can run the next transformations on.
   I have renamed the reference table in my example to "Summary Table".
   
2. Step two - Pivot Columns.
   In the transformations tab, select "Pivot Column", then use Date as your Values Column and Count (ALL) as your aggregate value function.
   
3. Add the three new columns together using a new column per below.
   
4. Delete the A, B and C columns as unnecessary.
   
   
5. Filter the "All Operations" Column to only include items with the number 3.
   
   This will give you the result you want but without the dates.
   
6. To add the dates back in, we can hit "Close and Apply" and head out of Power Query.
   Jump into your model view and set up the relationship between your old table and the new summary table. The join should be on ProductID to ProductID with a many to 1 relationship.
   
7. And thats it!
   Now if you go into the report view, select the table visual and use the Date Field from your original data table, with the ProductID field from your summary table. Open the visual filters and set ProductID to "Is Not Blank" And you get the result 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!

[midmurali]

Hey @MDodds,

This seems to work fine! Thank you for your help!

Midhun

[MDodds]

No problem! Please mark my solution as accepted if helpful.

Cheers!

[Manoj_Nair]

@midmurali 

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.

[midmurali]

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

[Manoj_Nair · Accepted solution]

Hi @midmurali - Pls find attached the PBIX link for reference. Please mark accepted if this fixed your requirement. Many Thanks