cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
v-uprav Frequent Visitor
Frequent Visitor

Matrix data based on currentmonth in mapping condition.

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?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Matrix data based on currentmonth in mapping condition.

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

5 REPLIES 5
Community Support Team
Community Support Team

Re: Matrix data based on currentmonth in mapping condition.

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
v-uprav Frequent Visitor
Frequent Visitor

Re: Matrix data based on currentmonth in mapping condition.

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

Community Support Team
Community Support Team

Re: Matrix data based on currentmonth in mapping condition.

Hi @v-uprav,

 

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

1.gif

 

 

Regards,

XIaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
v-uprav Frequent Visitor
Frequent Visitor

Re: Matrix data based on currentmonth in mapping condition.

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

Community Support Team
Community Support Team

Re: Matrix data based on currentmonth in mapping condition.

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)