Forum Discussion

v-uprav's avatar
v-uprav
Frequent Visitor
8 years ago
Solved

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 a...
  • Anonymous's avatar
    Anonymous
    8 years ago

    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-uprav's avatar
v-uprav
Frequent Visitor
8 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi v-uprav,

     

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

     

     

    Regards,

    XIaoxin Sheng

    • v-uprav's avatar
      v-uprav
      Frequent Visitor
      8 years ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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