How to get "Offset" data by using measure in Power BI

[sunyangjun1115]

Hi folks,

i have 2 tables in Power BI report as below,

• Table 1 contains material data, it has 2 columns : "Material Code" and "Material Name"

Material Code	Material Name
9960026	Steel
9960049	Aluminm
99600053	Wood
350200	Plastic
4500200	Glass
6850000	Concrete
94020000	Rubber

• Table 2 contains Purchase Order Data . it has 3 columns. "PO Number", "Material Code" and "Qty".  each PO contains multiply materials, and a material can be exist under mulitply PO.

PO Number	Material Code	Qty
58003289	9960026	123
58003289	9960049	333
58003289	99600053	222
58040307	350200	666
58040307	4500200	777
58049777	350200	44
58049777	4500200	23
59005219	6850000	12
59005219	94020000	45
59007819	6850000	23
59007819	94020000	12
59009989	6850000	11
59009989	94020000	333

these 2 tables use Column "Material Code" to build the relationship .

 My purpose is when user choose a material code(i can put a slicer in report, and bind to material table) ,  it can show the associated PO numbers and the material codes which are having the same PO number but the different materials codes.   i can use DAX query to show the data. but i don't how to show the data in the report with a Table Visual by using measure.

 below screenshot shows the steps to get the result in Excel. for your information.  in Power BI , i need user to choose a material code , and then a table visual will show the similar data in step 3 of below screenshot.  the column "Material Name" also need to be added to the Table Visual in report.

Thanks in advance for your time and support. 

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file whether it suits your requirement.

total qty expected measure: =
VAR _slicerlist =
    VALUES ( slicer_material[Material Code] )
VAR _targetponumber =
    CALCULATETABLE (
        SUMMARIZE (
            FILTER (
                SUMMARIZE (
                    fact_purchase_order,
                    fact_purchase_order[PO Number],
                    dim_material[Material Code]
                ),
                dim_material[Material Code] IN _slicerlist
            ),
            fact_purchase_order[PO Number]
        ),
        REMOVEFILTERS ( dim_material )
    )
VAR _exceptslicerlist =
    FILTER (
        SUMMARIZE (
            fact_purchase_order,
            fact_purchase_order[PO Number],
            dim_material[Material Code]
        ),
        fact_purchase_order[PO Number]
            IN _targetponumber
                && NOT ( dim_material[Material Code] IN _slicerlist )
    )
RETURN
    CALCULATE (
        SUM ( fact_purchase_order[Qty] ),
        TREATAS (
            _exceptslicerlist,
            fact_purchase_order[PO Number],
            dim_material[Material Code]
        )
    )

[sunyangjun1115]

Hi Kim,

Thanks so much for your prompt support.  your understanding for my requirement is 100% correct.  i tested the measure within the PBIX file that you shared , it works as expected. 

the only thing that i found strange is that when i add the column "Material_Code" from Table "fact_purchase-order" into the Table Visual .  the data in the Table Visual will disappear.  but if i choose the column "Material_Code" from table "dim_material", it works. see below screen.

since in my actual case, the fact table(similar as  table "fact_purcahse_order" in this demo) contains some other columns that i also need to add to Table Visual for visualize. i have tried to choose some of columns and added into the Table Visual, the result is that no data will show.  the same issue as the demo that i encountered. 

[Jihwan_Kim]

Hi,

Thank you for your reply.

In my opinion, the measure that I have created works with material code column only from the dimension table -> REMOVEFILTERS(dimension table[column])
Try REMOVEFILTERS() from the fact table as well.

total qty expected measure: = 
VAR _slicerlist =
    VALUES ( slicer_material[Material Code] )
VAR _targetponumber =
    CALCULATETABLE (
        SUMMARIZE (
            FILTER (
                SUMMARIZE (
                    fact_purchase_order,
                    fact_purchase_order[PO Number],
                    dim_material[Material Code]
                ),
                dim_material[Material Code] IN _slicerlist
            ),
            fact_purchase_order[PO Number]
        ),
        REMOVEFILTERS ( )
    )
VAR _exceptslicerlist =
    FILTER (
        SUMMARIZE (
            fact_purchase_order,
            fact_purchase_order[PO Number],
            dim_material[Material Code]
        ),
        fact_purchase_order[PO Number]
            IN _targetponumber
                && NOT ( dim_material[Material Code] IN _slicerlist )
    )
RETURN
    CALCULATE (
        SUM ( fact_purchase_order[Qty] ),
        TREATAS (
            _exceptslicerlist,
            fact_purchase_order[PO Number],
            dim_material[Material Code]
        )
    )

[sunyangjun1115]

Hi Kim,

thanks so much for your kindly reply. 

Followed by your instruction, it works after the code "REMOVEFILTERS(fact_purchase_order[Material Code])" is added into the measure.  it means that for column "Material Code", no matter which table i choose it from. the table Visual will always shows the right data.  

many thanks for your time and great support.  it has resolved my problem.  

I have doubts about the measure when it is added to the table Visual.   could you please explain a bit more about how the value of the measure is calcuated when we add  the columns to the Table Visual . for example,  we choose column "PO Number" and "Material Code" from table "fact_purchase_order" and add into the Table Visual.  and then we also add the measure that you created into the Table Visual.  why we can get the materials which are having the same purchase order with the "selected Material ", i know the measure can calcuate the total qty of the materials which are having the same PO number with the selected material. and if i put this measure to the "Card" Visual in the report, the result can very easy undertand. my question is when we put the measure and cloumns together in a "Table " Visual. how it works. why it can show the data(only the materials which have  the same PO Number with the selected material )  ,what is the principle?