cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
JIGAR
Resolver IV
Resolver IV

Summarize by one column ignoring other columns

Hello, 

 

I need some help with restructing one of my measures. I am trying to summarize a table by a particular column and calculating the MIN datetime, but I would like to ignore other column. Here is the example of what I am trying to do.

 

Actual Data 

 

LevelCustomerJoin DatefilterRecords
BasicA1/31/2022 10:151
PremiumA1/31/2022 10:351
BasicB1/31/2022 10:551
BasicB1/31/2022 10:251
PremiumB1/31/2022 11:351
BasicA1/31/2022 10:150
PremiumA1/31/2022 10:350
BasicC1/31/2022 10:151
PremiumC1/30/2022 10:351
BasicD1/31/2022 10:551
BasicD1/31/2022 10:251
PremiumD1/28/2022 11:351
BasicC1/31/2022 10:150
PremiumC1/31/2022 10:350
BasicE1/31/2022 10:551
BasicE1/31/2022 10:251
PremiumE1/21/2022 11:351

 

This is a subset of how my data looks like. In this (filterRecords) is one of the measure that I have created based on certain criteria. 

 

Below is my desired result

 

Basic2
Premium3
Total5

 

I want to calculate total number of customers based on the condition where filterRecords= 1 and then I need to count the customers based on their minimum join date MIN (joinDate). Currently my measure is returning two records for customer A as there are 2 Levels against customer A.

 

I would like the measure to return only one record for customer A based on the MIN(joinDate). 

 

Here is the measure I have tried.

 

customerCount =
var tempTable1 =
CALCULATETABLE(
Table,
FILTER( Table, Table[_filterRecords] = 1 ))
var tempTable2 =
SUMMARIZE(tempTable1,Table[Customer],"Min Date", CALCULATE(MIN(Table[JoinDate]), ALLEXCEPT(Table, Table[Customer])))
RETURN
COUNTROWS(tempTable2)

 

Can someone please help me with this measure ? I am not sure what I am doing wrong here.

 

Any help on this would be greatly appreciated.

 

Thank you

 

Regards

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

You were pretty close.

 

 

 

 

customerCount =
VAR c =
    CALCULATETABLE (
        'Table',
        ALLEXCEPT ( 'Table', 'Table'[Level] ),
        'Table'[filterRecords] = 1
    -- only consider valid records 
VAR j =
    ADDCOLUMNS (
        c,
        "min join",
            VAR d = [Customer]
            RETURN
                CALCULATE ( MIN ( 'Table'[Join Date] ), ALL ( 'Table' ), 'Table'[Customer] = )
    -- calculate earliest join date 
VAR f =
    FILTER ( j, [Join Date] = [min join] -- only consider rows for min join date 
RETURN
    COUNTROWS ( )

 

or all in one: 

 

customerCount =
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            CALCULATETABLE (
                'Table',
                ALLEXCEPT ( 'Table', 'Table'[Level] ),
                'Table'[filterRecords] = 1
            ),
            "min join",
                VAR d = [Customer]
                RETURN
                    CALCULATE ( MIN ( 'Table'[Join Date] ), ALL ( 'Table' ), 'Table'[Customer] = )
        ),
        [Join Date] = [min join]
    )
)

 

 

 

  

 

 

 


 

View solution in original post

2 REPLIES 2
Vera_33
Resident Rockstar
Resident Rockstar

Hi @JIGAR 

 

When you summarize your temp table on Customer, there is no context of Level...I did add a Calculated column instead

Vera_33_0-1647216790106.png

Check = 
VAR T1=FILTER(ALL('Table'),'Table'[filterRecords]=1&&[Customer]=EARLIER('Table'[Customer]))
VAR T2=GROUPBY(T1,[Customer],"minDate",MINX(CURRENTGROUP(),[Join Date]))
RETURN 
IF([Join Date]=MAXX(T2,[minDate]),1,0)

customerCount = CALCULATE(COUNTROWS(VALUES('Table'[Customer])),'Table'[Check]=1)

 

lbendlin
Super User
Super User

You were pretty close.

 

 

 

 

customerCount =
VAR c =
    CALCULATETABLE (
        'Table',
        ALLEXCEPT ( 'Table', 'Table'[Level] ),
        'Table'[filterRecords] = 1
    -- only consider valid records 
VAR j =
    ADDCOLUMNS (
        c,
        "min join",
            VAR d = [Customer]
            RETURN
                CALCULATE ( MIN ( 'Table'[Join Date] ), ALL ( 'Table' ), 'Table'[Customer] = )
    -- calculate earliest join date 
VAR f =
    FILTER ( j, [Join Date] = [min join] -- only consider rows for min join date 
RETURN
    COUNTROWS ( )

 

or all in one: 

 

customerCount =
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            CALCULATETABLE (
                'Table',
                ALLEXCEPT ( 'Table', 'Table'[Level] ),
                'Table'[filterRecords] = 1
            ),
            "min join",
                VAR d = [Customer]
                RETURN
                    CALCULATE ( MIN ( 'Table'[Join Date] ), ALL ( 'Table' ), 'Table'[Customer] = )
        ),
        [Join Date] = [min join]
    )
)

 

 

 

  

 

 

 


 

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors