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 Number Rep...
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:

     

     

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

     

     

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

     

     

     

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