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

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?