2 dimensions lookup on the same data table, I need help please.

[cuongtrinh2197]

The dashboard: 

https://shorturl.at/ZgEy3

In my data. batch number for product 1 are Batch 1-X, same as for product 2,3,4.

I have fact data table fData with 3 categories:
- From warehouse: WH1 & to Warehouse: WH2 indicate the SENT ITEMS

- From warehouse: WH2 & to Warehouse :BLANK & Line remark: BLANK indicate the USED ITEMS

- From warehouse: WH2 & to Warehouse: BLANK & Line remark: Expired indicate the EXPIRED ITEMS

In the 1st tab, I want to create 2 tables:

 Picture 1

My purpose is when I click to one line of expired table, the result will return to all the lines in sent table contain the same Product batch number.

And the second tab I want a table like that to compare the sent, used and expired quantity

Please give me an idea for those issue because all the data working on the same data table and my company doesn't allow me to work on data table or modify the relationship,... only use measure to create visual.

[cuongtrinh2197]

Thank you very much, that's not what I need at the moment because users will lookup at the Product number instead of Batch number. But thanks a lot for you help!

[dk_dk]

Hi @cuongtrinh2197 ,


For the second part of your request:

I assume your source data looks something like this:

To achieve the table you want, you will need 3 measures created:

Sent = CALCULATE(SUM(fData[Quantity]),fData[From warehouse]="WH1",fData[To Warehouse]="WH2")'

Used = CALCULATE(SUM(fData[Quantity]),fData[From warehouse]="WH2",fData[To Warehouse]="",fData[Line remark]="")

Expired = CALCULATE(SUM(fData[Quantity]),fData[From warehouse]="WH2",fData[To Warehouse]="",fData[Line remark]="Expired")

Instead of  To Warehouse = "" you might need to use the ISBLANK function, depending on how the blank value is represented in your data.

Then you can create a matrix visual, where the row is the Product column from your fData table, and the values are the 3 measures. (the Column is empty).

Let me know if this works for you or if you have any questions.


Regarding the first part of your request, I think it should be possible, but I will need to experiment with it a little bit. I will get back to you if I find a solution.


Best regards,
Daniel

[cuongtrinh2197]

Thank you very much Daniel,

Your explanation is very detailed and my dashboard runs smoothly, 

I'm looking forward to the rest solution.

Appreciate that !

[dk_dk · Accepted solution]

Hi again,


I spent a bit of time on the first part and could not figure out a way to make it work with the limitations you have (not modifying the dataset, measures only).

I would suggest that you can use a slicer on the page for Product Batch, and instead of clicking on rows in the Expired table, the user would have to find a row, check the batch number, and select it in the slicer. With the sample dataset from before:

Hope this helps!