Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
MJEnnis
Helper V
Helper V

Adding Column in a Calculated Table by Filtering a Second Table

‎01-25-2022 02:00 AM

Does any one see why the following code works for ID 26 and 21, but not 27 and 15? It probably has something to do with timestamps being identical for some entries, as the code works for instances where the correct output does not have a duplicated timestamp. The extract is the last ADDCOLUMN comand of a much longer code for a calculated table. The rest of the code works perfectly up to this point. Any quick and easy soutions would be much aprreciated!

EXTRACT:
[...]

IF([L1] = "Italian"
&& NOT([Current Level English] = "unknown")
&& NOT([Current Level German] = "unknown")
&& ISBLANK([Current Level Ladin])
&& [English Current Level Order] = [German Current Level Order],
CALCULATE(
MAX(
'All Language Certifications'[Cert Language]),
FILTER(
'All Language Certifications',
'All Language Certifications'[Cert EXLO ID] = [EXLO ID]
&& 'All Language Certifications'[Cert Date] = CALCULATE(MIN('All Language Certifications'[Cert Date]),
FILTER(All(
'All Language Certifications'),
'All Language Certifications'[Cert EXLO ID] = Earlier(
'All Language Certifications'[Cert EXLO ID])
&& 'All Language Certifications'[Cert Language Order Level] = [German Current Level Order]
&& 'All Language Certifications'[Cert Language] <> "Italian")))
),
[...]

RETURNED TABLE (Has many more columns/rows)
Current Level EnglishCurrent Level GermanCurrent Level ItalianCurrent Level LadinEXLO IDL1L2
B2B2C1 27ItalianItalian
C1C1C1 26ItalianGerman
C1C1C1 15ItalianItalian
B2B2C1 21ItalianGerman

 

All Language Certifications (has many more rows)

Cert EXLO IDCert DateCert LanguageCert Language Order LevelCert Institute IDCert Exam Board
1523.05.14 14:29English867Other
1505.05.14 18:42Italian837High School-University
1523.05.14 14:27German826Province of Bozen-Bolzano
1523.05.14 14:27Italian826Province of Bozen-Bolzano
2129.04.15 11:48Italian837High School-University
2108.05.15 00:00German61UNIBZ
2112.01.21 00:00English61UNIBZ
2610.03.16 11:35German831Goethe Institut
2610.03.16 11:22Italian837High School-University
2610.03.16 11:49English862Cambridge
2610.03.16 11:46German826Province of Bozen-Bolzano
2610.03.16 11:46Italian826Province of Bozen-Bolzano
2709.04.16 14:50Italian837High School-University
2709.04.16 14:56Italian626Province of Bozen-Bolzano
2709.04.16 14:56German626Province of Bozen-Bolzano
2709.04.16 14:56English662Cambridge

 

The problem is that L1 and L2 cannot be identical. 15 should have an L2 of "German" and 17 could be "English" or "German" based on the logic at hand. (I will improve that part later.)

 

Thanks in advance!!

Message 1 of 6
354 Views
0
5 REPLIES 5
MJEnnis
Helper V
Helper V

‎01-25-2022 05:43 AM

This seems to do the trick. But I will offer the "accepted solution" to anyone who can find errors or optimize: 

IF([L1] = "Italian"
&& NOT([Current Level English] = "unknown")
&& NOT([Current Level German] = "unknown")
&& ISBLANK([Current Level Ladin])
&& [English Current Level Order] = [German Current Level Order]
&& NOT(ISBLANK(CALCULATE(
SELECTEDVALUE(
'All Language Certifications'[Cert Language]),
FILTER(
'All Language Certifications',
'All Language Certifications'[Cert EXLO ID] = [EXLO ID]
&& 'All Language Certifications'[Cert Language Order Level] = [German Current Level Order]
&& 'All Language Certifications'[Cert Language] <> "Italian"
&& 'All Language Certifications'[Cert Date] = CALCULATE(MIN('All Language Certifications'[Cert Date]),
FILTER(All(
'All Language Certifications'),
'All Language Certifications'[Cert EXLO ID] = Earlier(
'All Language Certifications'[Cert EXLO ID])
&& 'All Language Certifications'[Cert Language Order Level] = [German Current Level Order]
&& 'All Language Certifications'[Cert Language] <> "Italian"
)))
))),
CALCULATE(
SELECTEDVALUE(
'All Language Certifications'[Cert Language]),
FILTER(
'All Language Certifications',
'All Language Certifications'[Cert EXLO ID] = [EXLO ID]
&& 'All Language Certifications'[Cert Language Order Level] = [German Current Level Order]
&& 'All Language Certifications'[Cert Language] <> "Italian"
&& 'All Language Certifications'[Cert Date] = CALCULATE(MIN('All Language Certifications'[Cert Date]),
FILTER(All(
'All Language Certifications'),
'All Language Certifications'[Cert EXLO ID] = Earlier(
'All Language Certifications'[Cert EXLO ID])
&& 'All Language Certifications'[Cert Language Order Level] = [German Current Level Order]
&& 'All Language Certifications'[Cert Language] <> "Italian"
)))
),
IF([L1] = "Italian"
&& NOT([Current Level English] = "unknown")
&& NOT([Current Level German] = "unknown")
&& ISBLANK([Current Level Ladin])
&& [English Current Level Order] = [German Current Level Order],
CALCULATE(
SELECTEDVALUE(
'All Language Certifications'[Cert Language]),
FILTER(
'All Language Certifications',
'All Language Certifications'[Cert EXLO ID] = [EXLO ID]
&& 'All Language Certifications'[Cert Language] <> "Italian"
&& 'All Language Certifications'[Cert Exam Board] = "Province of Bozen-Bolzano"
&& 'All Language Certifications'[Cert Language Order Level] = [German Current Level Order]
&& 'All Language Certifications'[Cert Date] = CALCULATE(MIN('All Language Certifications'[Cert Date]),
FILTER(All(
'All Language Certifications'),
'All Language Certifications'[Cert EXLO ID] = Earlier(
'All Language Certifications'[Cert EXLO ID])
&& 'All Language Certifications'[Cert Language] <> "Italian"
&& 'All Language Certifications'[Cert Exam Board] = "Province of Bozen-Bolzano"
&& 'All Language Certifications'[Cert Language Order Level] = [German Current Level Order]
)))
),
Message 4 of 6
289 Views
0
AlexisOlson
Super User
Super User
In response to MJEnnis

‎01-25-2022 01:56 PM

I think using variables will make it easier to keep track of what's going on.

 

Here's a refactoring attempt using some variables:

a =
VAR _EXLO = [EXLO ID]
VAR _Level = [German Current Level Order]
VAR _Date1 =
    CALCULATE (
        MIN ( 'All Language Certifications'[Cert Date] ),
        ALL ( 'All Language Certifications' ),
        'All Language Certifications'[Cert EXLO ID] = _EXLO,
        'All Language Certifications'[Cert Language Order Level] = _Level,
        'All Language Certifications'[Cert Language] <> "Italian"
    )
VAR _1 =
    CALCULATE (
        SELECTEDVALUE ( 'All Language Certifications'[Cert Language] ),
        KEEPFILTERS ( 'All Language Certifications'[Cert EXLO ID] = _EXLO ),
        KEEPFILTERS ( 'All Language Certifications'[Cert Language Order Level] = _Level ),
        KEEPFILTERS ( 'All Language Certifications'[Cert Language] <> "Italian" ),
        KEEPFILTERS ( 'All Language Certifications'[Cert Date] = _Date1 )
    )
VAR _Date2 =
    CALCULATE (
        MIN ( 'All Language Certifications'[Cert Date] ),
        ALL ( 'All Language Certifications' ),
        'All Language Certifications'[Cert EXLO ID] = _EXLO,
        'All Language Certifications'[Cert Language Order Level] = _Level,
        'All Language Certifications'[Cert Language] <> "Italian",
        'All Language Certifications'[Cert Exam Board] = "Province of Bozen-Bolzano"
    )
VAR _2 =
    CALCULATE (
        SELECTEDVALUE ( 'All Language Certifications'[Cert Language] ),
        KEEPFILTERS ( 'All Language Certifications'[Cert EXLO ID] = _EXLO ),
        KEEPFILTERS ( 'All Language Certifications'[Cert Language Order Level] = _Level ),
        KEEPFILTERS ( 'All Language Certifications'[Cert Language] <> "Italian" ),
        KEEPFILTERS ( 'All Language Certifications'[Cert Exam Board] = "Province of Bozen-Bolzano" ),
        KEEPFILTERS ( 'All Language Certifications'[Cert Date] = _Date2 )
    )
RETURN
    IF (
        [L1] = "Italian"
            && NOT ( [Current Level English] = "unknown" )
            && NOT ( [Current Level German] = "unknown" )
            && ISBLANK ( [Current Level Ladin] )
            && [English Current Level Order] = _Level,
        IF ( ISBLANK ( _1 ), _2, _1 )
    )
Message 5 of 6
268 Views
MJEnnis
Helper V
Helper V
In response to AlexisOlson

‎01-27-2022 12:42 AM

Thanks for the suggestion. Something like this would reduce the lines of code and improve performance. Unfortunately, it will not work. Table1 does not exist as a table in the data model. It is a table expression in the calculated table code. (Apologies if this was not clear from my posts.) So certain functions, like SELECTCOLUMNS() and ADDCOLUMNS() can be used to build upon it, but other more basic functions cannot. For instance I cannot create the first three variables, as [EXLO ID] and [German Current Level Order] cannot be found. Maybe there is a way to create these as a measure in the model?

Message 6 of 6
217 Views
0
MJEnnis
Helper V
Helper V

‎01-25-2022 05:36 AM

Maybe I have figured it out... both the table and the MIN() function need to be filtered...

 

Message 3 of 6
291 Views
0
MJEnnis
Helper V
Helper V

‎01-25-2022 02:24 AM

NOTE: The "order levels" are in a saved table, but not included the returned table. C1 = 8 and B2 = 6.

Message 2 of 6
325 Views
0

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

View All
Top Solution Authors
View All