Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

sql query to dax equivalent

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.

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        Anonymous
        Not applicable
        4 years ago

        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.