Forum Discussion

devesqdeves's avatar
devesqdeves
Helper II
2 years ago

Aggregate column values per Client Id in a matrix

Hello! I am having a hard time dealing with this problem, really hope that someone can help me out. -------------------------------------------------------------- Main goal is to be able to have a...
Need Help
lbendlin's avatar
lbendlin
Super User
2 years ago

Of course not, that field is part of the rows, not the values.  That is not possible with the standard visual, unless you use the column totals for it (but then it would be on the far right)

 

 

devesqdeves's avatar
devesqdeves
Helper II
2 years ago

is there a way to make it possible , using another visual? to have the total in the left?

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    Yes, but I would not recommend that. It will be a tremendous effort both now and for maintenance later, with no flexibility.  Rather consider using separate visuals.

  • devesqdeves's avatar
    devesqdeves
    Helper II
    2 years ago

    lbendlin 

    Oh i see , thanks a lot for your help and for your time.
    If you dont mind just helping me with another subject that is getting my head rolling and i dont understand why, i would really appreciate.

     

    - I want to count the rows of DW FCTEndOfYearEndings , where [FilterDateHCFACCurrentDate] = 1 and  the 'DW FCTEndOfYearEndings'[CandidateId] is different from the DW LeaveDayDim'[CandidateId] (with no relationship) where [FilterDateLeavesCurrentDate]=1)

    - This is the measure i am using to count , but is giving me blank
    CountRows = 

    CALCULATE(
        COUNTROWS('DW FCTEndOfYearEndings'),
        FILTER(
            'DW FCTEndOfYearEndings',
            [FilterDateHCFACCurrentDate] = 1 &&
            NOT(
                CONTAINS(
                    'DW FCTEndOfYearEndings',
                    'DW FCTEndOfYearEndings'[CandidateId],
                    CALCULATE(values('DW LeaveDayDim'[CandidateId]),FILTER('DW LeaveDayDim',[FilterDateLeavesCurrentDate]=1))
                )
            )
        )
    )
     
    Measures :
    FilterDateHCFACCurrentDate =
    VAR _table =

    if(SELECTEDVALUE('Date'[Year])=BLANK() && SELECTEDVALUE(MonthSorted[MonthName])=BLANK(),
                ((MAX ('DW FCTEndOfYearEndings'[ProjectPlayerRealEndDate])  >= TODAY() || MAX ('DW FCTEndOfYearEndings'[ProjectPlayerRealEndDate]) = BLANK())
                    && MAX ('DW FCTEndOfYearEndings'[TrueStartDate])  <= TODAY()),
      
                ((MAX ('DW FCTEndOfYearEndings'[ProjectPlayerRealEndDate])  >= _date || MAX ('DW FCTEndOfYearEndings'[ProjectPlayerRealEndDate]) = BLANK())
                    && MAX ('DW FCTEndOfYearEndings'[TrueStartDate])  <= _date ))
           

    RETURN
        IF (_table, 1, 0 )
     
    -----------------------------------------------------------------------
     
    FilterDateLeavesCurrentDate =
    VAR _table =

    if(SELECTEDVALUE('Date'[Year])=BLANK() && SELECTEDVALUE(MonthSorted[MonthName])=BLANK(),
                max('DW LeaveDayDim'[Day])=TODAY(),
                max('DW LeaveDayDim'[Day])=MAX ( 'Date'[Date] ))
    RETURN
        IF (_table, 1, 0 )
     
     
    Can you take a look please ?

     

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
    If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
    Please show the expected outcome based on the sample data you provided.

    If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    You are trying to use a measure as a table filter.  You need to first add that measure to each row of the table (or to an aggregation) before you can filter on it.

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago
    FILTER(
        'DW FCTEndOfYearEndings',
        [FilterDateHCFACCurrentDate] = 1 ...
  • devesqdeves's avatar
    devesqdeves
    Helper II
    2 years ago

    lbendlin But thats what i have in the measure , 

    Measure_=

    CALCULATE(
        COUNTROWS('DW FCTEndOfYearEndings'),
        FILTER(
            'DW FCTEndOfYearEndings',
            [FilterDateHCFACCurrentDate] = 1 &&
            NOT(
                CONTAINS(
                    'DW FCTEndOfYearEndings',
                    'DW FCTEndOfYearEndings'[CandidateId],
                    CALCULATE(values('DW LeaveDayDim'[CandidateId]),FILTER('DW LeaveDayDim',[FilterDateLeavesCurrentDate]=1))
                )
            )
        )
    ).
    What should i do differently?