Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 | |||
Name | ID company | Sales | Date |
Company 1 | 1111111111 | 100 | 01.01.2021 |
Company 1 | 1111111111 | 50 | 01.01.2021 |
Company 1 | 1111111111 | 80 | 01.01.2021 |
Company 2 | 1111111112 | 50 | 01.01.2021 |
Company 3 | 1111111113 | 40 | 01.01.2021 |
Company 4 | 1111111114 | 40 | 01.01.2021 |
Company 4 | 1111111114 | 30 | 01.01.2021 |
Company 5 | 1111111115 | 80 | 01.01.2021 |
Company 1 | 1111111111 | 70 | 02.01.2021 |
Company 1 | 1111111112 | 40 | 02.01.2021 |
Company 7 | 1111111117 | 80 | 03.01.2021 |
Company 2 | 1111111112 | 50 | 03.01.2021 |
Company 3 | 1111111113 | 40 | 03.01.2021 |
Company 4 | 1111111114 | 40 | 03.01.2021 |
Company 4 | 1111111114 | 30 | 03.01.2021 |
Company 6 | 1111111116 | 80 | 03.01.2021 |
Company 8 | 1111111118 | 80 | 03.01.2021 |
Result:
Table2 | |
Name | ID company |
Company 1 | 1111111111 |
Company 5 | 1111111115 |
Table3 | |
Name | ID company |
Company 7 | 1111111117 |
Company 6 | 1111111116 |
Company 8 | 1111111118 |
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)
Solved! Go to 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
)
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.
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
)
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.
Thanks, it is that i need.