Forum Discussion

TylerVono's avatar
TylerVono
Helper III
3 years ago
Solved

Comparing Two Columns in Different Tables

Hello, Here is my situation I need help with:

 

I have two columns I need to compare, one column: 'MOM Stocking Report': Shows all stocked items & 'Demand Seg': Shows the complete list of purchased items.

 

I need to compare them against one another to know which ones are stocked and which are not.

 

As you can see the equation below, it appears to get me halfway there as the pie chart does capture the data, however I am not certain this is correct, Also when I click on the 'YES' stocked pie chart, the table shows nothing. Also, technically the 'Agreement Level' should be a 0 if 'Not Stocked' or come back with null.

 

My relationship cardinality is at Many to Many , it wont let me change- I have removed all duplicates from Power Query. So I am basically out of ideas:

 

 

My Stock or Not Equation:

Stocking Agreement? = if(isblank(countx(filter('MOM Stocking Report','MOM Stocking Report'[Item Number]='Demand Seg'[ITEM NUMBER]),'MOM Stocking Report'[Item Number])),"YES","NO")

 

 

 

 

 

 

Need Help
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi, TylerVono 

    According to your description, when you select the "yes" in the pie chart , the matrix visual return null .And the [Stocking Agreement?] i think it is a calcualted column in your 'Demand Seg' Table.

    I test in my side , In your case, when you select "Yes", it does return null, because the item does not exist in your 'MOM Stocking Report' table.

    If you want to display non-existent items as well, you need to put the 'Table'[ITEM NUMBER] who has the total ITEMNUMBER and then you need to select the "Show items with no data" options so that you can see the all ITEMNUMBER . If you want to see "0" instead of the null , you can also create a measure like this to replace this fields.

    Measure =  SUM('MOM Stocking Report'[Agreement Level])+0

     

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

1 Reply

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    Community Support
    3 years ago

    Hi, TylerVono 

    According to your description, when you select the "yes" in the pie chart , the matrix visual return null .And the [Stocking Agreement?] i think it is a calcualted column in your 'Demand Seg' Table.

    I test in my side , In your case, when you select "Yes", it does return null, because the item does not exist in your 'MOM Stocking Report' table.

    If you want to display non-existent items as well, you need to put the 'Table'[ITEM NUMBER] who has the total ITEMNUMBER and then you need to select the "Show items with no data" options so that you can see the all ITEMNUMBER . If you want to see "0" instead of the null , you can also create a measure like this to replace this fields.

    Measure =  SUM('MOM Stocking Report'[Agreement Level])+0

     

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly