cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
midmurali
Helper I
Helper I

Display table contents for particular column value with conditions

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):

 

midmurali_3-1669984265094.png

 

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:

midmurali_2-1669984243507.png

 

Any help is highly appreciated.

 

Thanks in advance!

M

 

3 ACCEPTED SOLUTIONS
MDodds
Resolver II
Resolver II

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".
    MDodds_0-1669985703586.png

     

  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.
    MDodds_1-1669986238060.png

     

  3. Add the three new columns together using a new column per below.
    MDodds_2-1669986330642.png

     

  4. Delete the A, B and C columns as unnecessary.

  5. Filter the "All Operations" Column to only include items with the number 3.
    MDodds_3-1669986430887.png

     


    This will give you the result you want but without the dates.
    MDodds_4-1669986476880.png

     

  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.
    MDodds_5-1669986589187.png

     

  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!
    MDodds_6-1669986748679.png

     



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!

 

 

View solution in original post

PaulDBrown
Super User
Super User

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)

 

result.jpg

(If you need this calculation by date, include the date field in the ALLEXCEPT expression in VAR _ABC)

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

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

View solution in original post

9 REPLIES 9
PaulDBrown
Super User
Super User

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)

 

result.jpg

(If you need this calculation by date, include the date field in the ALLEXCEPT expression in VAR _ABC)

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






DimaMD
Super User
Super User

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


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

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
Resolver II
Resolver II

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".
    MDodds_0-1669985703586.png

     

  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.
    MDodds_1-1669986238060.png

     

  3. Add the three new columns together using a new column per below.
    MDodds_2-1669986330642.png

     

  4. Delete the A, B and C columns as unnecessary.

  5. Filter the "All Operations" Column to only include items with the number 3.
    MDodds_3-1669986430887.png

     


    This will give you the result you want but without the dates.
    MDodds_4-1669986476880.png

     

  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.
    MDodds_5-1669986589187.png

     

  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!
    MDodds_6-1669986748679.png

     



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!

 

 

Hey @MDodds,

 

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

 

Midhun

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

Cheers!

@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

Manoj_Nair_4-1669998129888.png

Then Output will be

Manoj_Nair_1-1669998057898.png

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

Manoj_Nair_2-1669998057900.png

Output 2

Manoj_Nair_3-1669998057902.png

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.