Help on Context Transition

[Jaweed_L]

Hi

I have a table as shown below.   TLeavesUnit is a Calculated Column  and the report is working fine. When I add a measure in the matrix for each staff, I have a multitude of irrelevant rows being populated.

I would like to add  the measure ( in red below) Expected Work Units, then another measure which is the sum of this measure and TLeaves Units ( a calculated Colunmn), keepimg only these 5 records shown. 

I am having all these records diplayed

I do not know why these records are shown. Please help me., been struggling with Calculate, KeepFilters etc, but cannot get it to work. Many Thanks

 Jaweed_L

[Jai-Rathinavel]

@Jaweed_L  Can you show the DAX used for Expected Work Units and TLeave Units ? 

[Jaweed_L]

@Jai-Rathinavel 

Thank you fotr your mail.

Expected Work Units = ('Calendar'[Days Between] - SUM( 'Calendar'[Sats And Suns]) - SUM( 'Calendar'[Public Holidays])) * 75

Basically it reads a calendar table, where Saturdays/Sundays/Public Holidays have been flagged. It gets the no of days between the dates selected and minus the non working days, then multiply by 75 ( in 1 day staff is expected to work 75 units) to compute the expected work units. This is working fine.

TLeaves is a calculated column, each time a staff records units against code of T leaves, the units are pushed in the T leaves column,

Even by defining a simplistic measure eg

Test Measure = 2, and inserting it in the pbi matrix, unwanted records will come out. 

If I remove the measure from the matrix, only required records will be display, which is correct.

Thanks to advise me.

Jaweed

[Jai-Rathinavel]

@Jaweed_L I believe this is happening because you have created "Expected Work Units" measure on a calendar table and pulling it against the Fact Table. Try a visual level filter like TLeave Units is not blank or make the relationship between calendar and fact table bidirectional but it is not advisable. To resolve this you have to create the measure for the Expected Work Units based on  both the Fact Table date column and Calendar Date column.

[Jaweed_L]

@Jai-Rathinavel 

Thank you. The measure is on the fact table not on the calendar.

[Jai-Rathinavel]

@Jaweed_L Though the measure is on the Fact Table it is still using all the columns from Calendar and not from Fact for calculation.

[Jaweed_L]

Thank you Sir.

But even if I create a measure not related to the calendar, nor any columns, the misbehavior persists.

I create a measure in the fact table and set its value to a constant 100. By inserting it in the matrix, irrelevant rows are displayed.

[Jai-Rathinavel]

@Jaweed_L If you are working on a sample pbix you can share the the PBIX file on this thread.

[Jaweed_L]

@Jai-Rathinavel , thank you so much for your eagerness to assist me.

Do you think we can have a Teams meeting where I can show/share with you the pbix file. 

Jaweed.Lallmahomed@datagazeltd.com

thanks.

[Jai-Rathinavel]

@Jaweed_L  All the communications should happen through this thread so that even if couldn't help you other community member will be able to help you out and others who are facing this issue will be able to refer this thread. You can share the pbix with mockup data here. 

[Jaweed_L]

@Jai-Rathinavel , I  get the point. I will prepare a mock pbix, as the present pbix file is quite huge.