Matrix data based on currentmonth in mapping condition.

[v-uprav]

I have a chicklet slicer which basically has Yes/No options which basically signifies selection of current month to be displayed or not. I have a matrix which has data coming from a table's measure as values, columns as monthname & rows as some titles. 

I have data in this table in such away that where one column Pickcurrent if this is "Yes" then the data will be all 7 months including current month Jan 2018, If its "No" then it will have all data except current month Jan 2018. 

I created a relationship between these two tables  as Many(measure data table) to one (chicklet slicer data table). 

But for some reasons its not working. Presently I am seeing data like this:

If picked Yes then I am seeing right data that is all 7 columns(Jul, Aug, Sep, Oct, Nov, Dec & Jan) (all with 0's or with data) - Expected

If picked No then I am seeing wrong data that is 7 columns (Jul, Aug, Sep, Oct, Nov, Dec & Jan) (all with 0's or with data) but expected result is 6 columns with No Jan column displaying. But for me Jan data is displaying with 0's. 

How should I make this Join work although join working on values but its not hiding the Jan data. 

Is there any option of making matrix columns visibility conditionally?

[v-uprav]

Hi Xiaoxin Sheng,

        I have the matrix set up with these fields:

Rows: Names (coming from one table which has ID and Name tablename: KPINames)

Columns: PeriodName (Jul 2017, Aug 2017,......)

Values: Measure (based on value of ID from KPIName I wrote a switch statement where I have data coming in from one table and did all summarizations, counts of the respectivie fields)

Now for instance we have data for ID 1, 7 months including current month that is Jan 2018. As I said earlier, I have chicklet slicer Yes/No options

Yes - it should display the data always (if curent month has data it will have numbers) or zeroes (if it doesn't has data).

(Here PeriodName - colums in matrix visual I did not pick "Show Items with no data" option)

If I pick "Show Items with no data" then zeroes will not displayed if it has no data.

No - it should avoid displaying the Jan data completely but this is not happening instead it is showing zeroes.

Regarding my measure formula, I am having one measure which has all summations and  then created another measure which has an If clause when the summations measure is blank then show it as zero and this is configured to the Values in Matrix visuals.

Please suggest where I am doing mistake here.

Thanks & Regards

Chary

[v-shex-msft]

Hi @v-uprav,

Maybe you can take a look at below sample if it suitable for your requirement:

Regards,

XIaoxin Sheng

[v-uprav]

Hi v-shex-msft,

   This exactly matches with my requirement. So here when no is clicked we should not see Jan 2018 column itself (hide Jan 2018 column in the matrix) but here it is showing with zeros. I think you got my requirement. But here how to hide Jan 2018 column in the matrix if No is picked.

Thanks

Chary

[v-shex-msft · Accepted solution]

HI @v-uprav,

You can try to use below formula, I added the condition to filter records when you choose 'No':

Total =
IF (
    SELECTEDVALUE ( Records[Current Month] )
        IN ALLSELECTED ( 'Selector'[Current Month] ),
    SUM ( Records[Amount] ),
    IF ( SELECTEDVALUE ( Selector[Current Month] ) = "NO", BLANK (), 0 )
)

Regards,

Xiaoxin Sheng

[v-shex-msft]

HI @v-uprav,

>>If picked No then I am seeing wrong data that is 7 columns (Jul, Aug, Sep, Oct, Nov, Dec & Jan) (all with 0's or with data) but expected result is 6 columns with No Jan column displaying. But for me Jan data is displaying with 0's. 

I think you need modify your formula to add a condition to check selected column to replace unmatched records to blank.(default, if current column records show blank, matrix visual will auto hide this column)

Sample:

Measure =
IF (
    SELECTEDVALUE ( Table[Date] ) IN ALLSELECTED ( Table[Date] ),
    'Original measure formula',
    BLANK ()
)

BTW, can you please provide more contents help use clarity your scenario and expected result?

Regards,

Xiaoxin Sheng