topic Re: Pretty much sure that I'm missing something in measure area in DAX Commands and Tips

Pretty much sure that I'm missing something in measure area

Susanta91 — Sun, 25 Sep 2022 08:26:46 GMT

1st Screenshot:

Please take a look of the above screen attached, both visual is being interacted by topn N slicer and material type external slicer & returns correct result. However, my requirement is something different.

Sharing some details about my reuirement:
1. In the top visual Material Type column I have used in Y-axis under Vendor & 2nd visual same Material Type field has been used as Legend.
2. In below screen, when I select any field from Material slicer it matches TOPN slicer selected value.

2. Screenshot

Now what I want is, likewise 1st screenshot (2nd visual) - I want to use material type field as legend & visual should also return TOPN selected value in one Bar all Material Type without selecting any field from Material external filter. for ex. in one Vendor name bar three material types( Material, ND, Services ) will be appeared.

To make it short: I want the following Visual to work correctly with top N slice which is not doing. Material Type field is coming another dimension table which linked one to many relationship with fact table & Vendor from different dimention table also concatenated field.

Used Measure:

Purchase_Vendors =

VAR TopFilteredVendor_TK =

CALCULATETABLE(

VALUES( T_VENDOR_DIM[Vendor TK] ),

TOPN(

[Top N Value],

ALL(T_VENDOR_DIM[Vendor TK]),

[3. PD Amount]

)

RETURN

SWITCH (

TRUE (),

ISINSCOPE( T_VENDOR_DIM[Vendor TK]), CALCULATE( [3. PD Amount], KEEPFILTERS ( TopFilteredVendor_TK )),

[Top N Value] = 0, [3. PD Amount]

)

If I miss someting to clarify please let me know. Will try to do as much as I could do. Demo PBIX File.

Re: Pretty much sure that I'm missing something in measure area

v-zhangti — Mon, 26 Sep 2022 06:59:10 GMT

Hi, Susanta91

Can you simplify your sample data for testing? And attach the output you expect.

Best Regards

Re: Pretty much sure that I'm missing something in measure area

Susanta91 — Mon, 26 Sep 2022 10:45:41 GMT

Hi, v-zhangti

Thanks for your response. I will try to make it simple & clear as suggest.
Simplified dataset to undertstand the requirement.
PFB the screenshot as well to point out scenario:

In Page 4 of demo dataset that is shared, I have taken this screen attached. In the left visual I have added two consolidated field as hierarchy (Both field are same Project Table) and used Material Type as Legend( coming from CC table ) different dimension table & used basic measure which is not interacting with TOP N Slicer beacuase I have not set it up.
However, I have placed same fields in the same position like 1st visual only changed the measure just to interact with TOP N slicer. Though TOP N returns the selected value for each Material Type.

My reuirement is:
1. Visual returns which value is selected in TOP N slicer irrespective of any Material Type. Purchased by Top N Project can be one of the Material Type(Material, ND, Services).
2. Whenever Drill Down next level hierarchy it should display based on TOP N Slicer.

3. If Drill Mode is on, end user wants to get details for any selected field & next level then same TOP N slicer selected number of Project needs to be displeyed.

Expected Output Image:

Hope I was able to explain you clearly for this time.