Forum Discussion

dexter2424's avatar
dexter2424
Icon for Helper I rankHelper I
5 years ago
Solved

Distinct Count Total issue based on Type and latest period

Hi All, 

 

I'm struggling with an issue, maybe it's easy to solve, or not, but I'm get lost, and the distinctcount doesn't work properly for me.

I have the following table

Policy NumberReporting PeriodType
A12021-01-01Large
A12021-02-01Not Large
A12021-03-01Large
A12021-05-01Large
B22021-01-01Not Large
B22021-03-01Not Large
B22021-05-01Large


I would like to distinct count the number of "Large" statuses with the following criteria:
I have a slicer, what shows the Max Reporting period date.
Once I select for example 2021-05-01 (YYYY-MM-DD), I would like to see the distinctcount result as 2, because on 2021-05-01 I have 2 Large values.
Once I select 2021-04-01, I would like to see 1, because A1 was Large on 2021-03-01 and B2 was Not Large on 2021-03-01. 
Once I select 2021-02-01, I would like to see 0, because A1 on the selected period is "Not Large", and B2 is also a "Not Large"

In summary, if there is an exact match with the dates, then count as 1 if the Type is "Large", and doesn't count if the Type is "Not Large", 
If there is no record in the selected period (for example A1 on 2021-04-01) then check the second highest date, what is 2021-03-01 and if it's "Large" count as 1, if it's "Not Large" count as 0.

Thank you very much in Advance!

 

Large = 
var _max = CALCULATE(MAXX(Table,Table[Reporting Period]),ALLEXCEPT(Table,Table[Policy Number],Table[Type]))

return

CALCULATE(DISTINCTCOUNT(Table[Policy Number]),FILTER(ALLEXCEPT(Table,Table[Policy Number]),Table[Type] = "Large" && Table[Reporting Period] = _max))

 

Need Help
  • DataInsights's avatar
    DataInsights
    5 years ago

    dexter2424,

     

    Try this solution.

     

    1. Create date table with a relationship to the data table:

     

     

    2. Create measure:

     

    Large = 
VAR vSlicerEndDate =
    LASTDATE ( ALLSELECTED ( Dates[Date] ) )
VAR vResult =
    SUMX (
        VALUES ( FactTable[Policy Number] ),
        VAR vMaxDate =
            CALCULATE (
                MAX ( FactTable[Reporting Period] ),
                FactTable[Reporting Period] <= vSlicerEndDate
            )
        VAR vType =
            CALCULATE ( MAX ( FactTable[Type] ), FactTable[Reporting Period] = vMaxDate )
        RETURN
            IF ( vType = "Large", 1, 0 )
    )
RETURN
    vResult

     

    3. Create date slicer using the date table (Dates[Date]).

     

    4. Create visual:

     

     

    ---------------------------------------------

     

     

    ---------------------------------------------

     

     

     

5 Replies

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    5 years ago

    dexter2424,

     

    Try this solution.

     

    1. Create date table with a relationship to the data table:

     

     

    2. Create measure:

     

    Large = 
VAR vSlicerEndDate =
    LASTDATE ( ALLSELECTED ( Dates[Date] ) )
VAR vResult =
    SUMX (
        VALUES ( FactTable[Policy Number] ),
        VAR vMaxDate =
            CALCULATE (
                MAX ( FactTable[Reporting Period] ),
                FactTable[Reporting Period] <= vSlicerEndDate
            )
        VAR vType =
            CALCULATE ( MAX ( FactTable[Type] ), FactTable[Reporting Period] = vMaxDate )
        RETURN
            IF ( vType = "Large", 1, 0 )
    )
RETURN
    vResult

     

    3. Create date slicer using the date table (Dates[Date]).

     

    4. Create visual:

     

     

    ---------------------------------------------

     

     

    ---------------------------------------------

     

     

     

    • dexter2424's avatar
      dexter2424
      Icon for Helper I rankHelper I
      5 years ago

      Thanks you DataInsights! That's what I'm looking for.

      In the meantime I have figured out another solution what is working, I just leave it here, maybe somebody findes it useful:

      Creating a max date measure:

      Max_Date = CALCULATE(MAXX(Table,Table[Reporting Period]),ALLEXCEPT(Table,Table[Policy Number]))

      and the main measure:

      Large = COUNTX(FILTER(Table,Table[Type] = "Large" && Table[Reporting Period] = [Max_Date]),Table[Policy Number])



    • Mi_80's avatar
      Mi_80
      Icon for Helper I rankHelper I
      3 years ago

      Hi,

       

      I am using this solution and it works great thanks.  I have a quick question.  If I wanted to display this measure in a bar chart instead with the value for each month acros x axis how could that be achieved?  Basically I would like to not have the slider but do the calculation for using the value of the first date of each month instead.  Thanks

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

    dexter2424 , Try a measure like

     

    measure =
    Var _max = maxx(allselected(Table), Table[Date])
    var _max1 = maxx(filter(Table, Table[date] <_max), Table[Date])
    var _cnt = countx(filter(Table, Table[Date] =_max && Table[Type] ="Large"),[Policy Number])+0
    var _cnt1 = countx(filter(all(Table), Table[Date] =_max1 && Table[Type] ="Large"),[Policy Number])
    return
    if(_cnt =0, _cnt1, _cnt)

    • dexter2424's avatar
      dexter2424
      Icon for Helper I rankHelper I
      5 years ago

      Thanks Amitchandak, but it still picks up the latest date.

      I have 4 different policies, and the highest date is 01-04-2021 there, all of the policies are unique, and for 3 of them the latest date is 01-04-2021 and one of them is 01-03-2021. The result of your query above gives me 3, because just counts the 3 highest ones, where the max date is 01-04-2021 and ignore the 01-03-2021 in another policy