sql query to dax equivalent

[Anonymous]

Hello,

I've been trying to solve an issue with my current dashboard and can't seem to find a solution.

I have the right result when i check it on the database side with sql, but I just seem to be unable to reproduce results in powerbi.
My table schema is basically 4 tables in one to many order:  Projecto->proj_etapa->operacao->qinv

and i need to reproduce the following query in powerBI:

SELECT COUNT(*)
     FROM(
     SELECT p.nome, count(distinct d.dimensao) as c
     FROM projecto as p
     JOIN proj_etapa as d
     ON p.nproj = d.nproj
     GROUP BY nome
having c>1) alias;

with the added detail that i have 4 slicers with 1 variable from each table to filter this data too...

I think it should be possible but i have no clue how to get to accurate result.

any help would be greatly appreciated.

[amitchandak]

@Anonymous , Try a measure like

Measure =
var _1 = calculate(distinctcount(proj_etapa[dimensao]))
return
sumx(filter(values(projecto[nome]), > _1), _1)

Both table should be joined in power bi

you can use nome in visual

[Anonymous]

@amitchandak, thanks for the reply
but i think there is something missing from your solution.

when you use  "filter(values(projecto[nome]), > _1)"

i assume you meant something like: filter(values(projecto[nome]), _1> 1).

if my assumption is right, it still gives me the wrong result.

it is giving me the total sum of "dimensao" for all projects instead of giving me the intended result.

[Anonymous · Accepted solution]

For someone looking for a possible solution to their problem, i eventually found it.

similar to what @amitchandak did but with a little tweak.

countx(

  filter(

    values(dbw_inv_projecto[nome]),

    calculate(

      distinctcount(dbw_inv_proj_etapa[dimensao])

    )> 1

  ),

  calculate(distinctcount(dbw_inv_proj_etapa[dimensao]))

)

the end result can be presented in a card. but if you're looking for input for

for a table, use @amitchandak 's solution.

hope this helps someone.