Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
There's an extremely similar equivalent for SQL's IN - I have no imagination, so an example extremely similar to the last one I actually wrote:
Card Sales = CALCULATE ( [total valid spend], FILTER ( payments, paymentypeid = 7 && paymentprocessorID IN { 1, 5, 6, 9 } ) )
NOT IN doesn't work - what's the simpliest DAX equivalent of SQL's NOT IN?
Thanks!
Solved! Go to Solution.
Hi @mmace1,
You need to place the NOT formula on the column and then use the in to make the level you need.
So you measure should look something like this:
MEASURE NOT IN = CALCULATE ( SUM ( Table1[Value] ); FILTER ( Table1; Table1[Category] = "C" && NOT ( Table1[CAT LEVEL] ) IN { 2; 3 } ) )
As you can see below in category C the total is 1500 if you took out the category 2 and 3 it's 1000.
Regarrds,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCard Sales = CALCULATE ( [total valid spend], FILTER ( payments, payments[paymentypeid] = 7 && not payments[paymentprocessorID] IN { 1, 5, 6, 9 } ) )
How hard is it to have "Not IN" ? At least for the sake of consistency !
I have a similar problem.
I want to return the plates that do not appear in this other table. However, the error below appears, follow my formula of the measure.
cars without service = CALCULATE (VALUES ('3A_CHAMADO_SERVICOS_CAL_VIEW (2)' [License Plate]);
FILTER (GWT_CARTAO_EQUIPAMENTO; NOT (
VALUES (GWT_CARTAO_EQUIPAMENTO [Plate])) in {VALUES ('3A_CHAMADO_SERVICOS_CAL_VIEW (2)' [Plate]))))
Helpp
Error Message:
MdxScript(Model) (37, 44) Erro de cálculo na medida '_Medidas'[carros sem serviço]:Uma tabela de vários valores foi fornecida, sendo que um único valor era esperado.
Hi @mmace1,
You need to place the NOT formula on the column and then use the in to make the level you need.
So you measure should look something like this:
MEASURE NOT IN = CALCULATE ( SUM ( Table1[Value] ); FILTER ( Table1; Table1[Category] = "C" && NOT ( Table1[CAT LEVEL] ) IN { 2; 3 } ) )
As you can see below in category C the total is 1500 if you took out the category 2 and 3 it's 1000.
Regarrds,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsI solved this by using excel as a source. Following this method I don't have to work with HTML code in my power query to get all the sheets
https://stackoverflow.com/questions/45945815/how-to-connect-google-sheet-with-power-bi
Hi @arneh,
Believe that you have make this answer on the incorrect post. This post has nothing to do with linking to google sheets or excel.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsTypo on your measure name (it's not 2 & 3, but the Measure title says not 1 & 2).
Exactly the syntax I was looking for, thanks!
Hi @mmace1,
Nice catch on the typo.
But other than that believe it works like you want correct?
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsYes, exactly - thanks!
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
116 | |
101 | |
88 | |
35 | |
35 |
User | Count |
---|---|
152 | |
100 | |
83 | |
63 | |
54 |