Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello, i have a problem in my sql view i have a situation like this:
Not all the ID_PRATICA have more than one COD_SERVIZIO_AEEG.
I need to create a calculate column where i need to now for every ID_PRATICA whit COD_SERVIZIO_AEEG=PN1 have also a second row whit the COD_SEVIZIO_AEEG=E01
how can i do it? thank you very much.
Solved! Go to Solution.
Hi,
I think you can do it like this
VAR _CurrentId = MyTable[ID_PRATICA]
VAR _CountForCriteria =
CALCULATE(
COUNTROWS( MyTable ),
MyTable[COD_SERVIZIO_AEEG] IN {"PN1","E01"},
MyTable[ID_PRACTICA] = _CurrentId
)
RETURN
IF(
_CountForCriteria >= 2,
1,
0
)
First, it stores the Current value of ID_PRATICA for the current Row you are looking at
Then, It counts how many rows we can find with COD_SERVIZIO_AEEG equals PN1 or E01 for this particular ID_PRATICA.
If its greater thant 2 then it means we found an ID with bother PN1 and E01.
Tell me if it works.
You need to replace MyTable with the name of your table
@Anonymous , Try like
New column =
var _1 = [ID_PRATICA]
var _2 = countx(filter(Table, [ID_PRATICA] =_1, [COD_SERVIZIO_AEEG]="PN1"),[ID_PRATICA])+0
var _3 = countx(filter(Table, [ID_PRATICA] =_1, [COD_SERVIZIO_AEEG]="E01"),[ID_PRATICA])+0
return
if(_2>0 and _3>0 , 1,0)
Hi @Anonymous,
You can use in operator and summarize function to achieve your requirement:
Search Tag =
VAR summary =
SUMMARIZE (
FILTER (
Table,
[ID_PRATICA] = EARLIER ( [ID_PRATICA] )
&& [COD_SERVIZIO_AEEG] IN { "E01", "PN1" }
),
[ID_PRATICA],
[COD_SERVIZIO_AEEG]
)
RETURN
IF ( COUNTROWS ( summary ) = 2, "Y", "N" )
Notice:
IN operator allow you to compare with the list of value and summarize can use to remove duplicate records.
m3tr01d function seems well but it will fail when your table existed duplicate records.(IN operator use 'OR' logic to compare records)
Regards,
Xiaoxin Sheng
Hi @Anonymous,
You can use in operator and summarize function to achieve your requirement:
Search Tag =
VAR summary =
SUMMARIZE (
FILTER (
Table,
[ID_PRATICA] = EARLIER ( [ID_PRATICA] )
&& [COD_SERVIZIO_AEEG] IN { "E01", "PN1" }
),
[ID_PRATICA],
[COD_SERVIZIO_AEEG]
)
RETURN
IF ( COUNTROWS ( summary ) = 2, "Y", "N" )
Notice:
IN operator allow you to compare with the list of value and summarize can use to remove duplicate records.
m3tr01d function seems well but it will fail when your table existed duplicate records.(IN operator use 'OR' logic to compare records)
Regards,
Xiaoxin Sheng
@Anonymous , Try like
New column =
var _1 = [ID_PRATICA]
var _2 = countx(filter(Table, [ID_PRATICA] =_1, [COD_SERVIZIO_AEEG]="PN1"),[ID_PRATICA])+0
var _3 = countx(filter(Table, [ID_PRATICA] =_1, [COD_SERVIZIO_AEEG]="E01"),[ID_PRATICA])+0
return
if(_2>0 and _3>0 , 1,0)
Hi,
I think you can do it like this
VAR _CurrentId = MyTable[ID_PRATICA]
VAR _CountForCriteria =
CALCULATE(
COUNTROWS( MyTable ),
MyTable[COD_SERVIZIO_AEEG] IN {"PN1","E01"},
MyTable[ID_PRACTICA] = _CurrentId
)
RETURN
IF(
_CountForCriteria >= 2,
1,
0
)
First, it stores the Current value of ID_PRATICA for the current Row you are looking at
Then, It counts how many rows we can find with COD_SERVIZIO_AEEG equals PN1 or E01 for this particular ID_PRATICA.
If its greater thant 2 then it means we found an ID with bother PN1 and E01.
Tell me if it works.
You need to replace MyTable with the name of your table
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |