Forum Discussion

HenryJS's avatar
HenryJS
Icon for Post Prodigy rankPost Prodigy
6 years ago

Measure: Blank and Count

Hi all,

 

I am using the below measure in a matrix. I would like to not show fields which are blank in the matrix. Also is it possible to have a count of the 'Yes' in the row subheaders?

 

 

Timesheet? =
IF (
SELECTEDVALUE ( 'Export Placements'[PlacementStartDate] )
<= MAX ( 'Calendar'[Date] )
&& SELECTEDVALUE ( 'Export Placements'[PlacementEndDate] )
>= MAX ( 'Calendar'[Date] ),
SELECTEDVALUE ( 'Missing Timesheets'[Status], "" ),
""
)
Data Protection
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    For blank change formula


    IF (
    SELECTEDVALUE ( 'Export Placements'[PlacementStartDate] )
    <= MAX ( 'Calendar'[Date] )
    && SELECTEDVALUE ( 'Export Placements'[PlacementEndDate] )
    >= MAX ( 'Calendar'[Date] ),
    SELECTEDVALUE ( 'Missing Timesheets'[Status], "" ),
    ""
    ) & ""

     

    And enable

     

    • HenryJS's avatar
      HenryJS
      Icon for Post Prodigy rankPost Prodigy
      6 years ago

      Hi amitchandak 

       

      I don't want to see rows with no "Yes" in. I also want this to work with te date slicer at the top.

       

      If I change the "" to BLANK() it shows now data - please see last screenshot.

       

      I also want to add a subtotal of "Yes" in the header rows if that's possible?

       

       

       

       

       

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi, HenryJS 

     

    Based on your description, I created data to reproduce your scenario.

    Test:

     

    You may create a measure like below.

     

    Result = 
var level1 = SELECTEDVALUE(Test[Level 1])
var level2 = SELECTEDVALUE(Test[Level 2])
var level3 = SELECTEDVALUE(Test[Level 3])
var tab = 
SUMMARIZE(
    ALLSELECTED('Test'),
    [Level 1],
    [Level 2],
    [Level 3],
    [Date],
    [Value],
    "Status",
    IF(
        [Value]>10,
        "Yes",BLANK()
    )
)
return

IF(
    ISFILTERED(Test[Date])&&ISFILTERED(Test[Level 1]),
    IF(
        SELECTEDVALUE(Test[Value])>10,
        "Yes",
        BLANK()
    ),
    IF(
        ISFILTERED(Test[Level 1])&&NOT(ISFILTERED(Test[Date])),
        IF(
            ISINSCOPE(Test[Level 3]),
            COUNTROWS(
                    FILTER(
                        tab,
                        [Status] = "Yes"&&
                        [Level 3] = level3
                    )
            ),
            IF(
                ISINSCOPE(Test[Level 2]),
                COUNTROWS(
                    FILTER(
                        tab,
                        [Status] = "Yes"&&
                        [Level 2] = level2
                    )
                ),
                COUNTROWS(
                    FILTER(
                        tab,
                        [Status] = "Yes"&&
                        [Level 1] = level1
                    )
                )
            )
        )
    )   
)

     

     

    'Show items with no data' is off. Here is the result.

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi, HenryJS 

     

    If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

     

    Best Regards

    Allan