Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
sunyangjun1115
Frequent Visitor

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

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 CodeMaterial Name
9960026Steel
9960049Aluminm
99600053Wood
350200Plastic
4500200Glass
6850000Concrete
94020000Rubber

 

  • 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 NumberMaterial CodeQty
580032899960026123
580032899960049333
5800328999600053222
58040307350200666
580403074500200777
5804977735020044
58049777450020023
59005219685000012
590052199402000045
59007819685000023
590078199402000012
59009989685000011
5900998994020000333

 

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

sunyangjun1115_2-1713098812247.png

 

 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.

sunyangjun1115_1-1713100833366.png

 

 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.

sunyangjun1115_0-1713100712417.png

 

Thanks in advance for your time and support. 

 

 

 

 

 

 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

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.

 

Jihwan_Kim_1-1713114338377.png

 

 

Jihwan_Kim_0-1713114320053.png

 

 

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

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


View solution in original post

4 REPLIES 4
Jihwan_Kim
Super User
Super User

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.

 

Jihwan_Kim_1-1713114338377.png

 

 

Jihwan_Kim_0-1713114320053.png

 

 

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

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


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.

 

sunyangjun1115_0-1713160687394.png

 

sunyangjun1115_1-1713160850320.png

 

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. 

 

 

 

 

 

 

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

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


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.  

 

sunyangjun1115_0-1713340304932.png

sunyangjun1115_1-1713341133830.png

sunyangjun1115_2-1713341258112.png

 

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? 

 

 

 

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.