Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Compare two dates (dates get from sliser). Show only unique IDs

Good day.
I have a table (table 1) that contains the following fields: company name, company ID, sales, date. Please note that there may be several identical IDs with sales data on the same day.
I would like to display the following result in two separate tables (taking into account the filter by year: there are two dates of the period, for example from 01.01.2021 to 03.01.2021. Slicer dates can be different):
Table 2: only company identifiers that only exist as of 01/01/2021 (compared to identifiers as of 03/01/20201
Table 3: only company IDs that only exist as of 03/01/2021 (versus 01/01/2021)

Table1   
NameID companySalesDate
Company 1111111111110001.01.2021
Company 111111111115001.01.2021
Company 111111111118001.01.2021
Company 211111111125001.01.2021
Company 311111111134001.01.2021
Company 411111111144001.01.2021
Company 411111111143001.01.2021
Company 511111111158001.01.2021
Company 111111111117002.01.2021
Company 111111111124002.01.2021
Company 711111111178003.01.2021
Company 211111111125003.01.2021
Company 311111111134003.01.2021
Company 411111111144003.01.2021
Company 411111111143003.01.2021
Company 611111111168003.01.2021
Company 811111111188003.01.2021

 

Result:

Table2 
NameID company
Company 11111111111
Company 51111111115

 

Table3 
NameID company
Company 71111111117
Company 61111111116
Company 81111111118

 

As a result, I created a measure (which I will use in the filter for table2), but the last line of code throws an error (when returning a row), can anyone give an idea?

 

 

Check_company =

var maxdate = MAXX (таблица1, [Дата])

var maxdate = MAXX (таблица1, [Дата])

var vminComp = calculatetable (значения ('table1' [ID компании], Filter (table1, [Date]) = mindate)

var vmaxComp = calculatetable (значения ('table1' [ID компании], Filter (table1, [Date]) = maxdate)

Var resultcompare = except (vminComp, vmaxComp)

var cCompany = SelectValue ('table1' [ID компании])

return IF (countrows (calcuatetable (resultcompare, resultcompare = cCompany))> = 1,1,0)

Result.JPG

1 ACCEPTED SOLUTION

Hi @Anonymous ,

You should know that calculated table could not be affected by the slicer first so if you want to create new tables to show, you can only write the date in the formula statically. Please refer the below sample file.

 

If you want to create measures to achieve it dynamically by slicer, you can create measures like this, put it in the table visual filter and set its value as 1:

Measure1 = 
VAR tab1 =
    SUMMARIZE (
        FILTER (
            ALL ( 'Table 1' ),
            'Table 1'[Date]
                >= CALCULATE ( MIN ( 'Table 1'[Date] ), ALLSELECTED ( 'Table 1' ) )
                && 'Table 1'[Date]
                    < CALCULATE ( MAX ( 'Table 1'[Date] ), ALLSELECTED ( 'Table 1' ) )
        ),
        'Table 1'[Name],
        'Table 1'[ID company]
    )
VAR tab2 =
    SUMMARIZE (
        FILTER (
            ALL ( 'Table 1' ),
            'Table 1'[Date]
                >= CALCULATE ( MAX ( 'Table 1'[Date] ), ALLSELECTED ( 'Table 1' ) )
        ),
        'Table 1'[Name],
        'Table 1'[ID company]
    )
VAR tb1 =
    EXCEPT ( tab1, tab2 )
RETURN
    IF (
        COUNTX ( FILTER ( tb1, [Name] IN DISTINCT ( 'Table 1'[Name] ) ), [Name] )
            = CALCULATE (
                DISTINCTCOUNT ( 'Table 1'[Name] ),
                ALLEXCEPT ( 'Table 1', 'Table 1'[Name] )
            ),
        1
    )
Measure2 = 
VAR tab1 =
    SUMMARIZE (
        FILTER (
            ALL ( 'Table 1' ),
            'Table 1'[Date]
                >= CALCULATE ( MIN ( 'Table 1'[Date] ), ALLSELECTED ( 'Table 1' ) )
                && 'Table 1'[Date]
                    < CALCULATE ( MAX ( 'Table 1'[Date] ), ALLSELECTED ( 'Table 1' ) )
        ),
        'Table 1'[Name],
        'Table 1'[ID company]
    )
VAR tab2 =
    SUMMARIZE (
        FILTER (
            ALL ( 'Table 1' ),
            'Table 1'[Date]
                >= CALCULATE ( MAX ( 'Table 1'[Date] ), ALLSELECTED ( 'Table 1' ) )
        ),
        'Table 1'[Name],
        'Table 1'[ID company]
    )
VAR tb2 =
    EXCEPT ( tab2, tab1 )
RETURN
    IF (
        COUNTX ( FILTER ( tb2, [Name] IN DISTINCT ( 'Table 1'[Name] ) ), [Name] )
            = CALCULATE (
                DISTINCTCOUNT ( 'Table 1'[Name] ),
                ALLEXCEPT ( 'Table 1', 'Table 1'[Name] )
            ),
        1
    )

re.png

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

I change last string for this:

Return if ( cCompany=calculate(allselected(table1 I Company), ID Company in resultCompare),1,0).

But it dont show  correct result ( show only 0). Can someone help?

Hi @Anonymous ,

You should know that calculated table could not be affected by the slicer first so if you want to create new tables to show, you can only write the date in the formula statically. Please refer the below sample file.

 

If you want to create measures to achieve it dynamically by slicer, you can create measures like this, put it in the table visual filter and set its value as 1:

Measure1 = 
VAR tab1 =
    SUMMARIZE (
        FILTER (
            ALL ( 'Table 1' ),
            'Table 1'[Date]
                >= CALCULATE ( MIN ( 'Table 1'[Date] ), ALLSELECTED ( 'Table 1' ) )
                && 'Table 1'[Date]
                    < CALCULATE ( MAX ( 'Table 1'[Date] ), ALLSELECTED ( 'Table 1' ) )
        ),
        'Table 1'[Name],
        'Table 1'[ID company]
    )
VAR tab2 =
    SUMMARIZE (
        FILTER (
            ALL ( 'Table 1' ),
            'Table 1'[Date]
                >= CALCULATE ( MAX ( 'Table 1'[Date] ), ALLSELECTED ( 'Table 1' ) )
        ),
        'Table 1'[Name],
        'Table 1'[ID company]
    )
VAR tb1 =
    EXCEPT ( tab1, tab2 )
RETURN
    IF (
        COUNTX ( FILTER ( tb1, [Name] IN DISTINCT ( 'Table 1'[Name] ) ), [Name] )
            = CALCULATE (
                DISTINCTCOUNT ( 'Table 1'[Name] ),
                ALLEXCEPT ( 'Table 1', 'Table 1'[Name] )
            ),
        1
    )
Measure2 = 
VAR tab1 =
    SUMMARIZE (
        FILTER (
            ALL ( 'Table 1' ),
            'Table 1'[Date]
                >= CALCULATE ( MIN ( 'Table 1'[Date] ), ALLSELECTED ( 'Table 1' ) )
                && 'Table 1'[Date]
                    < CALCULATE ( MAX ( 'Table 1'[Date] ), ALLSELECTED ( 'Table 1' ) )
        ),
        'Table 1'[Name],
        'Table 1'[ID company]
    )
VAR tab2 =
    SUMMARIZE (
        FILTER (
            ALL ( 'Table 1' ),
            'Table 1'[Date]
                >= CALCULATE ( MAX ( 'Table 1'[Date] ), ALLSELECTED ( 'Table 1' ) )
        ),
        'Table 1'[Name],
        'Table 1'[ID company]
    )
VAR tb2 =
    EXCEPT ( tab2, tab1 )
RETURN
    IF (
        COUNTX ( FILTER ( tb2, [Name] IN DISTINCT ( 'Table 1'[Name] ) ), [Name] )
            = CALCULATE (
                DISTINCTCOUNT ( 'Table 1'[Name] ),
                ALLEXCEPT ( 'Table 1', 'Table 1'[Name] )
            ),
        1
    )

re.png

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Thanks, it is that i need.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.